BookmarkSubscribeRSS Feed
MingZuo
SAS Employee

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. 

 

2018-12-06_15-59-53.jpg

 

Any advice would be appreciated.

 

Thanks!

Ming 

2 REPLIES 2
Sam_SAS
SAS Employee

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:

 

previous.png

 

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
PROC Star

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;

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 2 replies
  • 594 views
  • 1 like
  • 3 in conversation