topic Re: Relative % in the Pivot table in QlikView

Relative % in the Pivot table

bimala0507 — Fri, 09 Jan 2015 07:56:26 GMT

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

Re: Relative % in the Pivot table

its_anandrjs — Fri, 09 Jan 2015 08:06:27 GMT

Hi,

Try expression for Value % column

sum(Value) / sum(TOTAL Value)

Regards

Anand

Re: Relative % in the Pivot table

jagan — Fri, 09 Jan 2015 08:06:59 GMT

Hi,

Please find attached file for solution

Regards,

Jagan.

Re: Relative % in the Pivot table

Colin-Albert — Fri, 09 Jan 2015 08:09:31 GMT

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

Re: Relative % in the Pivot table

bimala0507 — Fri, 09 Jan 2015 08:46:29 GMT

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

jagan — Fri, 09 Jan 2015 08:52:21 GMT

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

Colin-Albert — Fri, 09 Jan 2015 08:53:15 GMT

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

SunilChauhan — Fri, 09 Jan 2015 08:55:27 GMT

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%

Re: Relative % in the Pivot table

bimala0507 — Wed, 14 Jan 2015 01:52:20 GMT

Thanks Jagan for providing the solution.

Kind Regards,

Bimala

Re: Relative % in the Pivot table

jagan — Fri, 16 Jan 2015 06:13:10 GMT

Hi,

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

Regards,

jagan.