topic Re: Expression label based on variable value in QlikView

Expression label based on variable value

Anonymous — Fri, 30 Aug 2013 09:26:48 GMT

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

Re: Expression label based on variable value

vikasmahajan — Fri, 30 Aug 2013 09:54:32 GMT

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

This will show you base on max data in database.

Vikas

Re: Expression label based on variable value

Fri, 30 Aug 2013 10:22:27 GMT

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

Re: Expression label based on variable value

Anonymous — Fri, 30 Aug 2013 10:58:26 GMT

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

Re: Expression label based on variable value

Fri, 30 Aug 2013 16:23:49 GMT

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.

Re: Re: Expression label based on variable value

Anonymous — Mon, 02 Sep 2013 10:39:16 GMT

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.

Re: Expression label based on variable value

Anonymous — Mon, 02 Sep 2013 12:01:46 GMT

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

Re: Expression label based on variable value

Fri, 20 Sep 2013 13:58:03 GMT

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,