Moving Average

A Moving Average, also called rolling average, rolling mean, or running average, calculates the average of the items (slices) within a specified interval. For example, if the interval size is set to three, the average is calculated on the current item and the two preceding items. The purpose of using a moving average is often to smooth out short-term fluctuations and to spot long-term trends.

Example

As starting point, the bar chart below is used. It shows the quarterly sums of sales for three years.

In the next bar chart, the shortcut expression Moving Average has been applied on the Value axis. (The arrow points to a bar used to explain what is calculated in the expression further down.)

The following expression is used on the Value axis (here the interval is set to 5).

Sum([Sales]) THEN Avg([Value]) OVER (LastPeriods(5,[Axis.X])) THEN If(Count() OVER (LastPeriods(5,[Axis.X]))=5,[Value],null)

The first part of the expression specifies that the column [Sales] should be aggregated as a sum. The result of this part goes into the [Value] column to be used in the expression following the first THEN.

The middle part of the expression calculates, for each node in the visualization (in this case each bar), the average of the node and the four previous nodes. The final part, after the second THEN, ensures that no moving average is shown if there are less than 5 bars.

For example, calculation of the Moving Average for Q2, 2021 (see the arrow in the previous image):

$350=($600+$400+$100+$350+$300)/5

(the sum of the node and the four previous nodes within the rectangle below divided by 5)

The column selector in expanded mode

In the installed client, the extended column selector offers controls that make it easy to change various parameters in the expression shortcut:


Display name	Optionally, specify a different display name instead of the default "Moving Average ([Column Name])".
Axis	Specify the axis over which to calculate the nodes. Only categorical axes that perform some kind of grouping can show up in the drop-down list. This means that if you only have a grouping on the X-axis, then this is the only axis available, whereas if you also have colored by a categorical column then the Color axis will be available as well, and so on.
Interval size	Define the size of the window to calculate the average within.
Exclude incomplete intervals	Determine whether to calculate averages for intervals that lack values for one or more of the nodes in the interval. If the check box is cleared, then the average value will be calculated using those values that are available only. If the check box is selected, nodes within incomplete intervals will be left empty.
Hide Empty Values	Creates a Show/Hide rule that hides all empty values. Click the properties symbol to access the Show/Hide properties to edit or remove the rule.