Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
vjoshi2017
Contributor
Contributor

How to show percentage of subtotal in pivot table?

I am trying to show percentage in pivot table in Qlik sense.

Pivot table.PNG

Quantity credit is how many numbers of a particular PNO are returned. For example, out of total sales for PNO 100330140, the last one, 124 are returned for various reasons.

the percentage returned is 9.68%, that means 1281 parts were sold before taking credits into account.

The next level is reason which tells us out of 9.68% how many were returned for different reasons.

reasons for return.PNG

This is what i wanted without selecting a part number, but on the pivot table itself. But when I expand pivot table for rows, I don't get desired results. I am getting percentage for whole data. Here I need 91.94%, 1.61%, and 6.45% for reasons C,F, and I respectively.

What I am getting is below :

Full pivot table.PNG

Formulae I am using for second measure (%Quantity Credited) is :

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total{<ORDTYPE={"CR"}>}TotalOrderQuantity)))


Please let me know how to get desired result.

Thanks,

Vivek

1 Solution

Accepted Solutions
boorgura
Specialist
Specialist

It should be

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total <PNO> {<ORDTYPE={"CR"}}TotalOrderQuantity)))

View solution in original post

4 Replies
boorgura
Specialist
Specialist

You are using TOTAL which will ignore all dimensions, but you should ignore the first dimension:

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total <first_Dim> {<ORDTYPE={"CR"}>}TotalOrderQuantity)))

vjoshi2017
Contributor
Contributor
Author

I used the following expression as well :

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total PNO{<ORDTYPE={"CR"}}TotalOrderQuantity)))

It doesn't work.

Mod.PNG

The thing is this work when I am selecting a part number.  the percentages are correct.  I believe there is an issue in aggregate because after selecting one part number, aggregate would be same.

Thanks,

Vivek

boorgura
Specialist
Specialist

It should be

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total <PNO> {<ORDTYPE={"CR"}}TotalOrderQuantity)))

vjoshi2017
Contributor
Contributor
Author

Thanks Rakesh