Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a problem where I can't get the percentage of each measure in Qlik sense pivot table. Calculation slightly differs for each measure. For measure 1 calculation is 6/12=50% (count(id)/(count({< myvalue={1} >} total id) and measure 2 3/11=27% (count(id)/(count(total id). Also it is important that it will appear as dimension and won't be distributed by months (just separate column).
Is it possible?
Hi Ernest
No simple way of doing all of that in a pivot table, I would suggest 2 possible approaches:
Approach 1 - Add (script) a aggregated table into your data model and do the calculations in the script (always better to carry complex calculations rather in the script), it would look something along the lines of:
SummaryMetrics:
LOAD
'Dimension1' AS Dimension,
Month AS Column,
'Measure1' AS Measure
MonthValue AS Value
FROM ...
CONCATENATE LOAD
'Dimension1' AS Dimension,
'Measure1' AS Measure,
'Total' AS Column
sum(MonthValue) AS Value
From ... Group BY
CONCATENATE LOAD
'Dimension 2' AS Dimension,
'Measure1' AS Measure,
'%' AS Column
sum(Value/Total) AS Value
Resident ... Group BY
Approach 2- Make use of the ValueList( function for the dimensions and the Pick(Match(ValueList( function for the measures in your object
See as an example solution in the post: https://community.qlik.com/t5/New-to-Qlik-Sense/ValueList-Expression/td-p/1718315
Hope this helps ...
Thanks for the reply!
In my case first approach won't be the solution because I have multiple different tables where this kind of column should be included.
Second approach doesn't seem to work when I have measures (measure1, measure2, measure3) instead of dimensions. I keep getting error - All parameters to ValueList must be constant.