KNOWLEDGE BASE

Return Nulls in Moving Average Calculation if there is Not Enough Data to Compute Average


Published: 18 Jan 2017
Last Modified Date: 27 Mar 2017

Question

How to show nulls, rather than a 'shortened average' for dates where there is less data than the X days required to complete a moving average?

 

Environment

Tableau Desktop

Answer

Create a new calculated field to replace the original moving average calculation using the formula below:

IF ISNULL(LOOKUP(SUM([Measure]),-X)) THEN NULL ELSE WINDOW_AVG(SUM([Measure]), -X, 0) END

"X" is the number of days that the moving average is computed from. This calculation allows the moving average to only be computed if there are at least X days worth of data, and will display a NULL if there aren't enough.
Did this article resolve the issue?