
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Expression label based on variable value
Hi all,
I implemented point-in-time -reporting with the same idea Mike describes here:
The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.
My calculations work based on autonumbered field MonthYearID (my version of Mike's PeriodID), with value 1 matching MonthYear 01_2007, value 2 matching MonthYear 02_2007, value 13 matching MonthYear 01_2008 etc.
I use sets stored in variables to do the calculations, and now I would like to show the used set in the expression label. If Year was 2010 and Month was May, the value for MonthYearID would be 41 and 05_2010 (value of the matching MonthYear-field) should be shown as the expression label.
I tried to use ApplyMap in the label calculation but it did not work.
Can someone think of an approach that would work?
Regards,
Juho
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks everybody, I solved the labeling in the following way (for 4 month-related columns):
First:
=FieldValue('MonthYear',$(=Max(MonthYearID)))
Second:
=FieldValue('MonthYear',$(=Max(MonthYearID)-1))
Third:
=FieldValue('MonthYear',$(=Max(MonthYearID) - 2))
Fourth:
=FieldValue('MonthYear',$(=Max(MonthYearID) - 13))
So I just take the MonthYearID-part from my variable (which is a number such as 77) and get the matching value of the MonthYear-field from my MasterCalendar-table based on the index number; MonthYearID 1 is 01_2007, 2 is 02_2007 etc.
-Juho

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=date(max(MonthYear), 'MMM-YYYY') & chr(10) & '(CM)'
This will show you base on max data in database.
Vikas
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Juho,
Based on the MonthYearID field, you can refernce the Month and Year field and use an expression for label as
num(Month)&'_'&Year'
Hope this helps
Thanks and BR,
Abhinava

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Vikas and Abhinava,
perhaps I was a bit unclear with my original post: the below image describes what I try to accomplish.
Selections at the time are May and 2013, so the first expression column shows sales according to the Tuotelaji-dimension for that month (for a specified customer not shown here). The second expression column shows sales for April 2013 and the third for March 2013.
Note the 77 in the first expression label; it is the MonthYearID used in the set analysis to calculate the values (725, 1470, 2780 and 0). I got the label by using =MonthYearID & ' 05_2013'
I used text 04_2013 and 03_2013 for the other labels, whose MonthYearIDs for set analysis are 76 and 75.
So the label should be the MonthYear-form (05_2013) of the variable value (77) used in the expression.
My variables for the three expressions shown are
vSetMTD
vSetPreviousMonth
vSetPreviousNMonth(2).
-Juho

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I understand correctly, you could use a variation on what Abhinava mentioned.
=MonthYearId & ' ' & num(Month) & '_' & Year
This -should- give you something like: 77 05_2013 - for your label.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, thats not it - I would basically just like to use a mapping function within the frontend, giving it the MonthYearID used in set analysis and getting the correct MonthYear in return.
From my picture; variable definition [set analysis] for column labeled '04_2013' is the following
MonthYearID = {$(=Max(MonthYearID) - 1)},
Year = ,
Quarter = ,
MonthYear = ,
Month =
That definition gives me value 76 for the MonthYearID, so I would like for it to automatically map it to 04_2013 for the label.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks everybody, I solved the labeling in the following way (for 4 month-related columns):
First:
=FieldValue('MonthYear',$(=Max(MonthYearID)))
Second:
=FieldValue('MonthYear',$(=Max(MonthYearID)-1))
Third:
=FieldValue('MonthYear',$(=Max(MonthYearID) - 2))
Fourth:
=FieldValue('MonthYear',$(=Max(MonthYearID) - 13))
So I just take the MonthYearID-part from my variable (which is a number such as 77) and get the matching value of the MonthYear-field from my MasterCalendar-table based on the index number; MonthYearID 1 is 01_2007, 2 is 02_2007 etc.
-Juho

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have a problem with this after. If I want to calculate difference between "Second" and "First" in new expression, it will not allowed me. Example:
If you do [Second]-[First] it works.
but in this occasion you can't use it as you have to put:
[=FieldValue('MonthYear',$(=Max(MonthYearID)))]-[=FieldValue('MonthYear',$(=Max(MonthYearID)-1))]
*they not getting blue [.....]
Any resolution?
Thanks,
