Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working in Qlik Sense, I have a ratio that I want to calculate comparing a line item measure to the subtotal of a different measure for the dimension.
Dimension A - Value 1
Measure 1 Measure 2 Desired Result
Item 1 10 20 10/100 = 10%
Item 2 20 30 20/100 = 20%
Item 3 10 50 10/100 = 10%
40 100 40/100 = 40%
Dimension A - Value 2
Measure 1 Measure 2 Desired Result
Item 1 15 20 15/100 = 15%
Item 2 30 30 30/100 = 30%
Item 3 5 50 5/100 = 5%
50 100 50/100 = 50%
This is the only way I have found to have the divisor be the sum of Measure 2 across the Dimension Value
SUM ( Measure 1 ) / SUM(TOTAL <Dimension A> Measure 2 )
Unfortunately, this requires me to declare an explicit value for Dimension A, is there a way to select the Dimension dynamically based on the Dimension the chart (Object) the expression is used in? Otherwise I fear I will have to declare it for each Dimension I want to use it in.
If you have a variable to store the selected field used as dimension you can use:
SUM (TOTAL <$(vDimensionSelected)> Measure2)
vDimensionSelected contains the field used as Dimension.
If you're only using one dimension then SUM ( Measure 1 ) / SUM(TOTAL Measure 2 ) should work too.
Good point, it does work with one Dimension in the Chart, but I am using a second dimension and creating a stacked bar, so I would be looking for a way to dynamically use the first dimension in the chart the measure is used in.
Hi Mark,
Are you trying to mimic the cycle dimension feature from Qlikview ?
In a sense, yes.
My goal is to not have to manually match the Dimension name in my chart to the Dimension clause of the TOTAL statement, which cycling would mimic.
If you have a variable to store the selected field used as dimension you can use:
SUM (TOTAL <$(vDimensionSelected)> Measure2)
vDimensionSelected contains the field used as Dimension.
True, then that would perform like cycling. I was planning to have multiple similar charts displaying so thought I may still have to manage more objects with a variable for each chart, but the number of charts I want to display compared to the number of dimensions I want to be able to run it by is much less, so makes sense. Thanks!
The variable works great in the expression, but having trouble getting the variable to be recognized as an actual dimension in the chart. I tried using an expression referencing the variable as well as creating a dimension object referencing the variable, but it just sees it as a unique text item with 1 value. Thoughts? Is there some additional syntax I need to use to pass it to the chart?
Glad to know it works for you.