Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kaanerisen
Creator III
Creator III

How to make dynamic percentage calculation for each group on pivot

Hi all,

I want to show percentage %ofParentTotal for each group on pivot table. I can make it by checking dimensionality of cell and calculate the %ofParentTotal for each group.


sum(VALUE)/

Pick(Dimensionality()+1,

sum(total VALUE),

sum(total VALUE),

sum(total <DIM1> VALUE),

sum(total <DIM1,DIM2> VALUE),

)

ss.png


but I need to calculate it without checking dimensionality. Actually, I don't want to edit the expression when any dimension is added. It is not effective


Is there any way to achieve this?


Thanks,


3 Replies
petter
Partner - Champion III
Partner - Champion III

I guess this is closer to what you are looking for?

2018-03-30 11_32_49-_ratio for each group - My new sheet _ App overview - Qlik Sense.png

petter
Partner - Champion III
Partner - Champion III

Furthermore you can make it dynamic as to which two first dimensions you put in the pivot by using the GetObjectField() function. It is a rather strange name for the function since it retrieves the name of the dimension you select:

Sum(VALUE)/SUM(TOTAL <$(=GetObjectField(0)),$(=GetObjectField(1))> VALUE)

This expression will adjust to whichever dimensions you put in the pivot.

Badzreyes00
Contributor III
Contributor III

Hi Petter,

Thanks for this idea! I have a bar chart that shows month on month 100% stacked bar chart and my formula is like:

count({<Status={'Approved'}>}distinct appl_no)
/
count({<Status={'Approved'}>}total <MonthYear> distinct appl_no)

NOW, I want to make my dimension into drill down MonthYear > Date but my formula fails ofcourse when drilling down into dates and unfortunately when I try GetObjectField it results into an error. Can you please advise? thank you!