topic Re: Dynamic Measure Labels based on Pivot Chart Dimensions in App Development

Dynamic Measure Labels based on Pivot Chart Dimensions

Qbo — Thu, 07 Apr 2022 12:24:58 GMT

Feel this might have been done to death at this stage but haven't found a workable solution yet and have tried a few different examples. Just figured it would be a useful topic to have anyway as I am sure other people are after the same thing

The best way I could explain it is if you have a use case for a pivot chart where you want to show the latest budget versus closed revenue across posted months of one year. Your usual 2+10, 4+8 etc. But you wanted the measure label to change dynamically e.g Actual Revenue for closed revenue and Budget Revenue for budgeted revenue what approach can you use?

Have tried the following:

Quickly worked out that Qlik doesn't recognise the pivoted dimension above in the label, only the measure itself
Tried to use the if statement from the measure which didn't work like this =if(floor([Posted Month])<floor(MonthStart(today())),'Actual Revenue','Budget Revenue')
Created a variable with basically the same expression that if it equals 1 displays the results, which works for the measure but again doesn't work for the label for example =if($(vBudgetMeasureLabel)=1,'Actual Revenue','Budget Revenue')
Have also tried these approaches in the condition of the measure but the issue seems to relate to the fact that outside of the measure itself Qlik doesn't seem to recognise the pivoted dimension

It may well be something simple I am missing, just haven't been able to get it to work.

I want to create a table that looks like this, bearing in mind I have the measure itself working and can change my approach there if necessary. The example below would be for this month so the last completed month of actuals is obviously March:

Any help would be much appreciated, thanks.

Re: Dynamic Measure Labels based on Pivot Chart Dimensions

justISO — Thu, 07 Apr 2022 12:45:47 GMT

Hi, do I understand correctly, that basically you have 2 types of sales, Actual and Budget, and (I hope) you can separate them? And then, according what day it is today you would like to change label, and switch sales between actual and budget. I will use part of your data like:

temp: load * inline [ date, product, sales, indicator 2022-01-01, wid, 2, B 2022-01-01, cat, 0, B 2022-02-01, wid, 56, B 2022-02-01, cat, 28, B 2022-03-01, wid, 58, B 2022-03-01, cat, 29, B 2022-04-01, wid, 114, B 2022-04-01, cat, 57, B 2022-05-01, wid, 172, B 2022-05-01, cat, 86, B 2022-06-01, wid, 286, B 2022-06-01, cat, 143, B 2022-07-01, wid, 458, B 2022-07-01, cat, 229, B 2022-08-01, wid, 744, B 2022-08-01, cat, 372, B 2022-01-01, wid, 111, A 2022-01-01, cat, 111, A 2022-02-01, wid, 222, A 2022-02-01, cat, 222, A 2022-03-01, wid, 333, A 2022-03-01, cat, 333, A];

and in report level I make simple pivot like this:

where I add this additional column with

=if (date<monthstart(today()), 'Actual', 'Budget')

and similarly adjusted sales measure to take actual or budget sales

Sum(if (date<monthstart(today()), if(indicator='A', sales),if(indicator='B', sales)))

Marked 'Expand all' in pivot properties.

Does this can do the trick in your situation?

Re: Dynamic Measure Labels based on Pivot Chart Dimensions

jbhappysocks — Thu, 07 Apr 2022 15:05:17 GMT

Hello

As far as I can see this:

=if(floor([Posted Month])<floor(MonthStart(today())),'Actual Revenue','Budget Revenue')

should work as long as you Posted Month field has a valid date format

This is what I am getting using it:

Re: Dynamic Measure Labels based on Pivot Chart Dimensions

Qbo — Fri, 08 Apr 2022 11:50:54 GMT

Thanks but not quite what I am looking for. I should have said, my model will have multiple measures or expression so for example say:

Revenue
Quantity
Unit Price

And I want the measure labels updated, in the end I had to use a solution similar to as you have. I was hoping I could use as ValueList() and then create 3 measures referencing it for each item but QlikView doesn't seem to want to play ball on updating expression labels based pivoted dimension criteria or anything useable from dynamic measure values either.

Think I was just getting too caught up expression labels when dimension splits were sufficient as more wanted to see if it could be done. Thanks for your help guys.