BookmarkSubscribeRSS Feed
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:


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.




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:




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?




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());



Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.


Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 


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
  • 1 like
  • 3 in conversation