Tip of the Week: Moving Calculation with Dynamic Window

Tip of the Week: Moving Calculation with Dynamic Window

Moving Calculations are ideal for analyzing data sets that fluctuate a lot. Be it stock prices, temperature, or daily sales trend, moving calculation allows you to identify the trend. It smoothens the noise of the random short-term fluctuations from your report.

In moving calculations, the value for each data point will be calculated over a specified time frame. For example, you are calculating a moving average over your stock price and have set the window as 10 days. Now your chart plots the 10days average stock price for each data point. Zoho Analytics supports moving calculations and also allows you to change the time frame in your report dynamically using Variables.

This week, let's see how to create moving calculations with dynamic window.

Supported Moving Calculations 

Zoho Analytics supports the following window calculations .

  • Window Sum
  • Window Average
  • Window Minimum
  • Window Maximum
  • Window Count
  • Window STD
  • Window Variance


Variables are placeholders that can be used as input, and can be changed on the fly. They enable dynamic input parameterization for various operations. With variables, you can set a dynamic window for your moving calculation.

To create a variable,

  1. From the Explorer page, click Settings .
  2. Open the Variables tab.
  3. Click the Create New Variable button.
  4. The Create New Variable dialog will open.
  5. Specify a name.
  6. Set the Data Typ e as Number .
  7. Select Range in the Define Value as field.
  8. Specify a range. Here, we've set a range of 5 to 50 with 2 as the default value.

  9. Click Create .

The variable is created. Now, we can use this to input a dynamic window for the moving calculation.

Create a Moving Average with Dynamic Window 

Follow the steps to create a chart with moving average with dynamic window.

  • Calculate moving average with a dynamic window
  • Create a moving average chart

Calculate Moving Average with a Dynamic Window 

Now, let's see how to calculate a moving average with a dynamic window.

  1. Open the data table.
  2. Click the Add > Aggregate Formula.
  3. The Add Aggregate Formula dialog will open.

  4. Specify the moving average formula with the variable as the window as in the following format.

    window_function( Aggregate Expression, Start, End )


    The following formula calculates the moving average of the stock price with a dynamic window.

    window_avg(avg( "index Prize"."Open" ), ${Window Range} , 0 )

  5. Click Save .

The formula is created. Now it can be used in the report.

Create a Moving Average Chart

Now let's create the moving average chart.

  1. Click Create > Chart View.
  2. Add the Date column with Full Date in X-Axis.
  3. Add the Moving Average Aggregate Formula and  Closed Prize  in Y-Axis.

  4. Add the variable as User Filter.

  5. Generate the report. Chart will plot 5 days moving average for everyday based on the variables default value.

You can drag the sliders to change the window for the report.

Explore the workspace from the below url.


Stay tuned to learn about more calculations. 

    Zoho Developer Community

                              Zoho Desk Resources

                              • Desk Community Learning Series

                              • Digest

                              • Functions

                              • Meetups

                              • Kbase

                              • Resources

                              • Glossary

                              • Desk Marketplace

                              • MVP Corner

                              • Word of the Day

                                  Zoho Marketing Automation

                                          Manage your brands on social media

                                                Zoho TeamInbox Resources

                                                  Zoho DataPrep Resources

                                                    Zoho CRM Plus Resources

                                                      Zoho Books Resources

                                                        Zoho Subscriptions Resources

                                                          Zoho Projects Resources

                                                            Zoho Sprints Resources

                                                              Qntrl Resources

                                                                Zoho Creator Resources

                                                                  Zoho WorkDrive Resources

                                                                    Zoho Campaigns Resources

                                                                      Zoho CRM Resources

                                                                      • CRM Community Learning Series

                                                                        CRM Community Learning Series

                                                                      • Tips


                                                                      • Functions


                                                                      • Meetups


                                                                      • Kbase


                                                                      • Resources


                                                                      • Digest


                                                                      • CRM Marketplace

                                                                        CRM Marketplace

                                                                      • MVP Corner

                                                                        MVP Corner



                                                                          Design. Discuss. Deliver.

                                                                          Create visually engaging stories with Zoho Show.

                                                                          Get Started Now