9 Replies Latest reply: Jan 16, 2015 1:13 AM by jagan mohan rao appala

# Relative % in the Pivot table

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

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

Total                16, 1000        1000/1800

Grand Total     26  1800

Appreciate a quick response.

Kind Regards,

Bimala

• ###### Re: Relative % in the Pivot table

Hi,

Try expression for Value % column

sum(Value) / sum(TOTAL Value)

Regards

Anand

• ###### Re: Relative % in the Pivot table

Hi,

Please find attached file for solution

Regards,

Jagan.

• ###### Re: Relative % in the Pivot table

Thanks Jagan for providing the solution.

Kind Regards,

Bimala

• ###### Re: Relative % in the Pivot table

Hi,

Regards,

jagan.

• ###### Re: Relative % in the Pivot table

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

• ###### Re: Relative % in the Pivot table

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

• ###### Re: Relative % in the Pivot table

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.

• ###### Re: Relative % in the Pivot table

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)

• ###### Re: Relative % in the Pivot table

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%