Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
juhoheino
Contributor III
Contributor III

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

1 Solution

Accepted Solutions
juhoheino
Contributor III
Contributor III
Author

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

View solution in original post

7 Replies
vikasmahajan

=date(max(MonthYear), 'MMM-YYYY') & chr(10) & '(CM)'

This will show you base on max data in database.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable

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

juhoheino
Contributor III
Contributor III
Author

Thanks Vikas and Abhinava,

perhaps I was a bit unclear with my original post: the below image describes what I try to accomplish.

Capture.PNG.png

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

Not applicable

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.

juhoheino
Contributor III
Contributor III
Author

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.

juhoheino
Contributor III
Contributor III
Author

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

Not applicable

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,