Hi, I'm trying to find the percentage difference of counts from previous period (eg previous year/quarter/month). I'm currently using the aggregated measure way in report designer where the percentage difference is easily generated by SAS VA. However, this approach will result in the first period percentage column in the crosstab to be empty (since there isn't any previous period). So for instance, the first period on my crosstab is 2011, the percentage difference between 2010 & 2011 will not be shown (refer to image) since 2010 is not in the crosstab and I'm not planning to display 2010 on the crosstab. However, I do have data for 2010.
The SQL code for the aggregated measure i'm currently using is
( RelativePeriod(_Sum_, 'NUM_CNT'n, 'DTE_CANCEL'n, _Inferred_, 0, _Full_, {Date}) - RelativePeriod(_Sum_, 'NUM_CNT'n, 'DTE_CANCEL'n, _Inferred_, -1, _Full_, {Date}) ) / Abs(RelativePeriod(_Sum_, 'NUM_CNT'n, 'DTE_CANCEL'n, _Inferred_, -1, _Full_, {Date}))
Hence, I'm thinking if it's possible to make the percentage difference statis by defining it in the data query whereby I have a list of percentage difference generated for different period and just place it in the table.
However, I'm not sure if this is possible or how to go about doing it.
I'm open to new ideas and suggestions. Any help will be greatly appreciated.
Thank you very much!
qiaojinggg:
Before I can possibly solve your questions, I need some clarification first.
For the 1st part of your question ... is this what you are after?
Wherever your Aggregated Measure formula results yield in . (aka dot, aka missing) … are you looking to hide the column(s)?
Meaning, are you just looking to get rid of the “Difference (YR)” 2011 column since it’s empty? Otherwise, the percentage difference calculation seems to work for you, is that correct?
For the 2nd part of your question ... are you asking if SAS Visual Data Builder would be better for your scenario vs. SAS Visual Analytics Designer? If so, I'll need clarification for the 1st part of your question (see above), before I can attempt this one as well.
Sincerely,
Ted Stolarczyk, SAS Customer Loyalty team
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.