Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
sarahallen1
Creator II
Creator II

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 ClassIncome AIncome BIncome X AccIncome 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)
20110-0-0-0-
20121,303,925071,231,5250100,05706,023,3940
2013494,645,4961,303,925108,991,44771,231,52565,211,097100,057420,197,4956,023,394
20141,293,868,935494,645,49632,736,634108,991,447306,152,35665,211,0971,545,748,172420,197,495
2015646,934,4681,293,868,93516,368,31732,736,634153,076,178306,152,356772,874,0861,545,748,172
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Have a look at the attachment.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

2 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Have a look at the attachment.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sarahallen1
Creator II
Creator II
Author

This is great!  Thanks very much Kaushik.  I never knew about the "above" function - will definitely be useful for lots of future problems too.  Thank you.