Relative % in the Pivot table

[bimala0507]

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

[its_anandrjs]

Hi,

Try expression for Value % column

sum(Value) / sum(TOTAL Value)

Regards

Anand

[jagan]

Hi,

Please find attached file for solution

Regards,

Jagan.

[Colin-Albert]

Sum(Value) / Sum(total <Category> Value)

[bimala0507]

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

[jagan]

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.

[Colin-Albert]

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)

[SunilChauhan]

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%

[bimala0507]

Thanks Jagan for providing the solution.

Kind Regards,

Bimala

[jagan]

Hi,

If you got the answer please close this thread by giving the Correct and Helpful Answers to the useful posts.

Regards,

jagan.