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

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.

https://analytics.zoho.com/workspace/1160261000007432001

Stay tuned to learn about more calculations. 


    Access your files securely from anywhere

        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 Campaigns Resources


                                                                          Zoho CRM Resources

                                                                          • CRM Community Learning Series

                                                                            CRM Community Learning Series


                                                                          • Kaizen

                                                                            Kaizen

                                                                          • Functions

                                                                            Functions

                                                                          • Meetups

                                                                            Meetups

                                                                          • Kbase

                                                                            Kbase

                                                                          • Resources

                                                                            Resources

                                                                          • Digest

                                                                            Digest

                                                                          • CRM Marketplace

                                                                            CRM Marketplace

                                                                          • MVP Corner

                                                                            MVP Corner





                                                                              Design. Discuss. Deliver.

                                                                              Create visually engaging stories with Zoho Show.

                                                                              Get Started Now