Calculate averages and moving averages

paul1034 — Fri, 30 Jan 2015 18:11:23 GMT

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!

Re: Calculate averages and moving averages

WouterHordijk — Tue, 17 Feb 2015 08:36:04 GMT

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.

topic Re: Calculate averages and moving averages in SAS Visual Analytics

Calculate averages and moving averages

Re: Calculate averages and moving averages