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,

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

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

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 )

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.

Click Create > Chart View.
Add the Date column with Full Date in X-Axis.
Add the Moving Average Aggregate Formula and Closed Prize in Y-Axis.
Add the variable as User Filter.
Generate the report. Chart will plot 5 days moving average for everyday based on the variables default value.