Percentage in Pivot table

[Ernests]

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?

[Jan-Hendrik]

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 ...

[Ernests]

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.