Previous year set analysis where year is a dimension

[sarahallen1]

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

[marcus_sommer · Accepted solution]

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

[sarahallen1]

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

[ashwanin]

I think this question is being answered in previous thread.

Where you have make that thread ok

fyi.. add =sum(YourField) in first expression and  =above() in second expression.