Calculate averages and moving averages

[paul1034]

Hi,

Is there a way calculate averages and moving averages in VA?  I'm trying to calculate how the current year-to-date totals (sales, profit, etc.) of a product or products compares to the average of the prior four years at this same point in the year?  For example, the current YTD sales through March is $1.5 million and the prior four year average through March is $1.2 million.

Any help is appreciated.  Thanks!

[WouterHordijk]

Assuming you have a date type category 'date' and you wish to calculate the current YTD sales and average YTD sales of the prior four years of the measure 'sales', you can create an aggregated measure named 'YTD sales (current)' containing:

CumulativePeriod(_Sum_, 'sales'n, 'date'n, _Inferred_, _ByYear_, 0, _Full_, {Date})

In addition, you can create an aggregated measure named 'YTD sales (average of prior four years)' containing:

( CumulativePeriod(_Sum_, 'sales'n, 'date'n, _Inferred_, _ByYear_, -1, _Full_, {Date})

+ CumulativePeriod(_Sum_, 'sales'n, 'date'n, _Inferred_, _ByYear_, -2, _Full_, {Date})

+ CumulativePeriod(_Sum_, 'sales'n, 'date'n, _Inferred_, _ByYear_, -3, _Full_, {Date})

+ CumulativePeriod(_Sum_, 'sales'n, 'date'n, _Inferred_, _ByYear_, -4, _Full_, {Date}) ) / 4

This will work, but I'm affraid this solution is not quite optimized: ideally you'd apply a RelativePeriod periodic aggregation to 'YTD sales (current)' in order to obtain the values for the prior four years in computing the average, but since it's already aggregated this will not work.