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
Hi,
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?
Kind Regards,
Bimala
Hi,
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))
Regards,
Jagan.
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.
Kind Regards,
Bimala
Hi,
If you got the answer please close this thread by giving the Correct and Helpful Answers to the useful posts.
Regards,
jagan.