Moving window formulas are aggregate formulas that allow you to calculate the average, max, min, or sum of your data over a predetermined interval, or window, with an adjustable range.
In this video, we will use a moving average formula to smooth out any irregularities in our data to easily recognize trends. The larger the interval we set, the more the peaks and valleys are smoothed out. While the smaller the interval, the closer the moving averages are to the actual data points.
Let’s start with a table showing the monthly sales for each department, spanning over five years.
In your search, click the Add Formula icon. You can use the Formula Assistant to view syntax and usage examples.
Enter a moving average formula with a measure, two integers to define the aggregation window over, and one or more attributes. The formula will return the average of the measure, which in our case is sales. The window is defined as the current row minus the value of the first integer to the current row plus the value of the second integer, with both end points included in the window. So in our example, both integers are one, which means we will have a window size of three. The attributes are the ordering columns used to compute the moving average. Here we have decided to order the data by departments and date. The sorting order should match the order represented in the table.
Click Save to apply your formula. You can edit your formula later by clicking the edit icon next to it, or delete it by clicking the x.
When we compare the results from the formula column with the actual data, we can see that the formula works correctly. For example, for the moving average for January 2016 it takes the average of monthly sales of frozen goods from December, January, and February 2016, which is 7,013.
Now, watch how the averages change when we add another moving formula with window values of one and four. This creates a window size of six months.
You can add more moving window formulas to get an even better picture of your data.