BookmarkSubscribeRSS Feed
paul1034
Calcite | Level 5


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!

1 REPLY 1
WouterHordijk
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1998 views
  • 0 likes
  • 2 in conversation