Back Dating Comparison on VA

[MingZuo]

Hi experts,

i would like to find a way to use the calculated item in VA to create a new column that reflects a previous time period value. Example is in the thread below:

https://communities.sas.com/t5/SAS-Programming/Back-dating-comparsion/m-p/519049#M140524

what I hope to achieve is the following dual bar chart, where comparison of product A's current month and previous month value could be made. 

Any advice would be appreciated.

Thanks!

Ming 

[Sam_SAS]

Hi Ming,

You can create this type of calculation by using the RelativePeriod operator to create an aggregated measure.

For example:

RelativePeriod(_Sum_, 'Expenses'n, _IgnoreInteractiveTimeFrameFilters_, 'Date by Month'n, _Inferred_, -1, _Full_, {Date})

The trouble is, the periodic operators require the date data item to be assigned to the object. It will return missing values otherwise.

If you were placing your date data item on the Category role, this would not be an issue, but you want to display a different category on the category axis.

A workaround is to assign your date data item to the Animation role:

Because we have a prompt that selects a single single value, the animation doesn't do anything. But the bar chart that is displayed should be what you want.

Another (possibly better) workaround would be to calculate the previous month value as part of your data preparation. Someone else might be better able to help you with the SAS or SQL code to do this.

Could that work for you?

Sam

[SASKiwi]

Our standard practice with monthly data being loaded into VA is to have not only the actual date being reported on but to also add a "Month Counter" column that is set to 0 for the current month, 1 for the previous month, 2 for 2 months back and so on. That way current versus previous month comparisons are easy - a filter selecting Month Counter of 0 and 1 will get the required data.

This approach works best where you completely reload your data each time you update.

This code snippet shows how to derive a month counter:

data want;
  set have;
  Reporting_Month = intck('MONTH', Reporting_Date, today());
run;