Find Percentage Difference in Data Query

[qiaojinggg]

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!

[TedStolarczyk]

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