Set Analysis - Previous Period % Change

[jcampbell474]

I'm trying to calculate the % difference in volume compared to the previous period.  I can compare to the previous year without issue.

Here is the syntax I'm using to get % difference to the same period, previous year:

=if(isnull([Period]),'-',(sum([Quantity])-Sum ({$<FiscalYear = {$(=Max (FiscalYear)-1)} >} [Quantity]))/Sum ({$<FiscalYear = {$(=Max (FiscalYear)-1)} >} [Quantity]))

When I change "FiscalYear" to "Period", nothing calculates.  Every example I can find online uses date formatting...I'm using Periods 1-13.  So, no date formatting is needed.  I just need to be able to select period 7 and it show the % change from period 6 to period 7.  If I just select the year, 2014 for example, I need it to show the % change in each period to the prior.  At this point, comparing period 1 to period 13 of the previous fiscal year is ideal, but not necessary.

Can anyone tell me how to accomplish this?

Thanks in advance!

[sunny_talwar]

the expression your wrote up there seems to work fine. Can you send a sample of what you are working on?

Best,S

[jcampbell474]

Unfortunately, I don't think I can slice up the app successfully enough to post a copy of this example.  I appreciate you testing my formula.

Why would the formula work using year, but not period?

[sunny_talwar]

Have you looked at the qvw file I attached? It uses the formula with the period only and it seems to work just right.

Best,

S

[jcampbell474]

Yes, I looked at it.  It does work.

I replace FiscalYear with Period in my file and goes to zero (0).  I removed everything and tried to get it to just calculate the previous year alone, using this: Sum ({$<Period = {$(=Max (Period)-1)} >} [Quantity]), but I still get zero's.  Seems like it doesn't want to decrease by 1 or maybe it's a formatting thing, I don't really know.

[kedar_dandekar]

Hi Jason,

Are you selecting the Year in your User Interface (and not the period), in that case you will have to modify the set analysis to ignore your Year Selection..

=if(isnull([Period]),'-',(sum([Quantity])-Sum ({$<FiscalYear=, Period = {$(=Max (Period )-1)} >} [Quantity]))/Sum ({$<FiscalYear =, Period= {$(=Max (Period )-1)} >} [Quantity]))

try it out..

HTH,

KD

[jcampbell474]

I tried it and got "-" in the results.  What you're saying makes sense and I'll probably have to incorporate your statement into the app after this issue is resolved.  Thank you for your help!

I'm now thinking it has to format.  The periods are all two characters.  01, 02, etc...  Formatted as text.  I can subtract one in an expression and it works fine.  I don't think it will work in a set analysis though.  I need to create a variable to convert Period to Num, but not having any luck with it.  Currently, it may be trying to subtract alpha from alpha.  If I convert it to number, set analysis may view it as number and return the previous period.

Could this be it?

[kedar_dandekar]

Jason,

This could be one of the several reasons.. if you can upload a small sample of your application, it would help to locate the exact problem.

Thanks,

KD

[jcampbell474]

A sample is attached.  Thank you in advance for taking a look at it.

Note - I intentionally loaded two columns of "Periods".  One is text. The other, number.  That's how the original data is formatted.

[kedar_dandekar]

Jason,

I understand why it was difficult with set analysis, when we add a dimension to the Pivot Table (e.g. FiscalYear) , it also filters data by that dimension (i.e. FiscalYear), and only the filtered data is available for the expressions.

So, for the row where FiscalYear = 2013, the data for that row gets filtered for 2013. Hence, even if we use multiple FiscalYears (2012, 2013) in our set analysis, it does not get data for 2012.

Also, the Set Analysis that we use, is evaluated once, i.e. not by each dimension row value.

To implement your task, I have used the Total function to ignore the dimensions while calculating the expressions, and in the Set Analysis I have used multiple If statements to take care of row by row calculation.

Please find attached the updated qvw: Prev Period Test - Total.qvw.

HTH,

KD