topic Re: How to calc % within the Group in a Pivot Table in QlikView

How to calc % within the Group in a Pivot Table

Anonymous — Fri, 18 Mar 2016 01:41:48 GMT

I have a Pivot table above: 2nd Expression "%" is where I need to get correct values. This colm sud give me Exp1 for ea Vendor divided by total of all vendors within ea Group. So "%" value for Vendor 1 sud have been = 13/29 ~ 45%. How do I get that?

In general I am trying to pull Top 5 vendor data within Group.

For Exp1 I am using sum(aggr(if((rank(count({ < fieldA = {'1'}>} ID))) <=5,count({< FieldA = {1}>} ID)),Group, Vendor))

For % I am using Exp1/count( TOTAL <Group>{< FieldA = {1}>} ID) but (this one is not working)

Re: How to calc % within the Group in a Pivot Table

sunny_talwar — Fri, 18 Mar 2016 01:45:14 GMT

May be this:

Sum(Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))/

Sum(TOTAL <Group> Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))

Re: How to calc % within the Group in a Pivot Table

Anonymous — Fri, 18 Mar 2016 02:11:08 GMT

Thats correct! sunny.

Taking that one more step ahead; If I want the denominator part of the % to be all Vendors within the Group, not just top 5, what wud the Expression be then?

For Eg. Group A has 6 vendors, with total count of 29+1 equal to 30. and I want the result to be 13/30, 4/30, and so on..

how it sud be handled?

Re: How to calc % within the Group in a Pivot Table

sunny_talwar — Fri, 18 Mar 2016 02:14:04 GMT

May be this:

Sum(Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))/

Sum(TOTAL <Group> Aggr(Count({<FieldA = {1}>} ID), Group, Vendor))

Re: How to calc % within the Group in a Pivot Table

Anonymous — Fri, 18 Mar 2016 02:22:22 GMT

Thank you!