Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Experts:
Can anyone help me to find out the relative % in a pivot table.
E.g. Let's say, I have the data like this
Category Product code Qty Value
Men, PR01, 1, 100
Men, PR02,4,300
Men, PR03,5,400
Ladies, PR05, 5, 200
Ladies, PR06,5,300
Ladies, PR07, 6, 500
I will display it in a pivot table enabling Partial sum
I would like introduce a column named %value which is Individual Value/Total Value under that Category
So it would be like
Category Product code Qty Value %
Men, PR01, 1, 100 100/800
Men, PR02,4,300 300/800
Men, PR03,5,400 400/800
Total 10, 800 800/1800
Ladies, PR05, 5, 200 200/1000
Ladies, PR06,5,300 300/1000
Ladies, PR07, 6, 500 500/1000
Total 16, 1000 1000/1800
Grand Total 26 1800
Appreciate a quick response.
Kind Regards,
Bimala
Hi,
Try expression for Value % column
sum(Value) / sum(TOTAL Value)
Regards
Anand
Please find attached file for solution
Regards,
Jagan.
Sum(Value) / Sum(total <Category> Value)
Hi Albert:
Sum(Value) / Sum(total Value), this worked. Now If I would
like to know how much each partial sum is as compared to Grand Total. How
do I achieve it?
Did you checked my file attached?
I used the expression in pivot =If(RowNo() = 0, Sum(Value)/Sum(Total Value), Sum(Value)/Sum(Total <Category> Value))
To get this working for partial sums, extend the list of dimensions within <> by adding each dimension field separated by commas. dim3 & dim4 are just examples below.
Sum(Value) / Sum(total <Category, [Product code], dim3, dim4> Value)
Num(Sum(Value) / Sum(total Value),'#%')
i thing this will give you % of total value
supose example
Emp Sales
1,100 -> 100/(100+200+300+400)*100 10%
2,200------>200/(100+200+300+400)*100---->20%
3,300------>300/(100+200+300+400)*100---->30%
4,400-----------.400/(100+200+300+400)*100---->40%
and total is 100%
Thanks Jagan for providing the solution.
If you got the answer please close this thread by giving the Correct and Helpful Answers to the useful posts.
jagan.
