3 Replies Latest reply: Feb 7, 2014 7:26 AM by Ashwani Kumar

# Previous year set analysis where year is a dimension

Hi,

Thanks very much for looking at my question.

I'm trying to do a set analysis expression to compare the annual total (dimension = year) with the annual total for the previous year.  NB my actual end-goal is much more complicated than this, but to troubleshoot I've simplified things as far as I can.

I can't get the set analysis for "previous year" to work. I've tried a few things, including TOTAL, different syntax for the modifier, putting PreviousYear field into script etc etc - see different tables in attached QVW.  I can get it to work as expected when I "hard code" the modifier, e.g. Year={"2012"} - but obviously I need the year to be dynamically the previous year, not fixed as 2012.

Any help would be greatly appreciated, thank you!

Sarah

NB here is my initial attempt (doesn't work - gives zero), and also the "hard coded" 2012 example (which works as expected).  A few other attempts can be seen in the QVW.

Initial attempt

 = sum(

{\$<  Year={\$(=only(Year)-1)}    >}

total <[Share Class]>

[Share Class NAV])

Hard coded 2012 example

 = sum(

{\$<  Year={"2012"}    >}

total <[Share Class]>

[Share Class NAV])

NB the expected result would be this:

 Share Class Income A Income B Income X Acc Income X Inc Year "Annual total NAV" (example expression) "PREVIOUS Annual total NAV" (example expression) "Annual total NAV" (example expression) "PREVIOUS Annual total NAV" (example expression) "Annual total NAV" (example expression) "PREVIOUS Annual total NAV" (example expression) "Annual total NAV" (example expression) "PREVIOUS Annual total NAV" (example expression) 2011 0 - 0 - 0 - 0 - 2012 1,303,925 0 71,231,525 0 100,057 0 6,023,394 0 2013 494,645,496 1,303,925 108,991,447 71,231,525 65,211,097 100,057 420,197,495 6,023,394 2014 1,293,868,935 494,645,496 32,736,634 108,991,447 306,152,356 65,211,097 1,545,748,172 420,197,495 2015 646,934,468 1,293,868,935 16,368,317 32,736,634 153,076,178 306,152,356 772,874,086 1,545,748,172
• ###### Re: Previous year set analysis where year is a dimension

I think you are looking for inter-record-functions like above(), below(), ... like this: = above(sum( [Share Class NAV])).

If you have year as dimension within the table you don't needed set analysis for year.

- Marcus

• ###### Re: Previous year set analysis where year is a dimension

Excellent - thank you very much Marcus.  The above() function works - I didn't know about that function, will definitely come in use.  Thanks.