Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Lokesh_5045
Creator
Creator

Reusing Master measures in another dimension in a table.

Hi Experts, I have two master measures like below. I need to compare both of them and flag them as 'Increase', 'Decrease' and 'Same'.

Master Measure 1: sum({< [Fiscal year/period TEXT] = {"$(vSelected_Month)"} >}  Quantity)
Master Measure 2: sum({< [Fiscal year/period TEXT] = {"$(=Date(Addmonths(Date#('$(vSelected_Month)','MMM YYYY'),-12),'MMM YYYY'))"}  Quantity)

The above 2 measures are sum of quantity of a selected month of current year and same month of last year. Both are working fine.

Now I need to create a dimension to flag based on difference between them.

=if([Master Measure 1] > [Master Measure 2], 'Increase',
If([Master Measure 1] < [Master Measure 2], 'Decrease',
'Same'))

Its not working. But when I create another measure it is working. But its not possible to select the values in a measure. Showing the screenshot below.

Lokesh_5045_0-1690699860468.png

In Measure field above, I can't make selection. So without using measures, how can I write the expression for dimension?

Labels (5)
2 Replies
Or
MVP
MVP

You would need to write this out using aggr() to assign the correct dimensions for the calculation - your screen capture isn't showing what they are - and you will need to spell out the formulas, as you won't be able to use the master measures, I believe.

Lokesh_5045
Creator
Creator
Author

Hi @Or , I was busy in this week. As you suggested, I tried using Aggr() to write the convert it into dimension. But I couldn't solve it. Please find the exact formulae that I have used for above columns in the screenshot.

1st column formula:
sum({<Region = {'*******'},
[Month] = {"$(=Date(Addmonths(Date#('$(vCurrent_Month)','MMM YYYY'),-12),'MMM YYYY'))"},
[Year] =
>}Tech_Qty)

2nd column formula:
sum({<Region = {'*******'},
[Month] = {"$(vCurrent_Month)"},
[Year]=
>}Tech_Qty)

I have used Region, Month & Year in my set analysis. Can you help me in writing aggr() formula to get required dimension as mentioned in the explanation?