Previous year set analysis where year is a dimension

sarahallen1 — Fri, 07 Feb 2014 11:01:34 GMT

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

marcus_sommer — Fri, 07 Feb 2014 12:20:10 GMT

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

sarahallen1 — Fri, 07 Feb 2014 12:23:57 GMT

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

Re: Previous year set analysis where year is a dimension

ashwanin — Fri, 07 Feb 2014 12:26:35 GMT

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.

topic Re: Previous year set analysis where year is a dimension in QlikView

Previous year set analysis where year is a dimension

Re: Previous year set analysis where year is a dimension

Re: Previous year set analysis where year is a dimension

Re: Previous year set analysis where year is a dimension