# 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.

Stay tuned to learn about more calculations.

# Zoho Developer Community

New to Zoho Survey?

Zoho Desk Resources

• Desk Community Learning Series

• Digest

• Functions

• Meetups

• Kbase

• Resources

• Glossary

• Desk Marketplace

• MVP Corner

• Word of the Day

Zoho SalesIQ Resources

Manage your brands on social media

Zoho Pagesense Resources

# Zoho CRM Resources

• CRM Community Learning Series

• Tips

• Functions

• Meetups

• Kbase

• Resources

• Digest

• CRM Marketplace

• MVP Corner

Design. Discuss. Deliver.

Create visually engaging stories with Zoho Show.

# Zoho Show Resources

Writer

# Get Started. Write Away!

Writer is a powerful online word processor, designed for collaborative work.

Zoho CRM コンテンツ