Getting % of sub groups in pivot tables using Aggr()

lucasdavis500 — Wed, 25 Nov 2020 16:16:04 GMT

Hello all, I have a pivot table where I'm trying to get % of the # worked, but I have two dimensions in the table so I'm not sure how I should go about this

Example Table: Currently

# Worked %
Name Type

John Doe Type1 50 -

Type2 100 -

Type3 10 -

Type4 40 -

Lucy Lou Type1 100 -

Type2 200 -

Type3 20 -

Type4 60 -

Both Name and Type are Dimensions. Name is a calculated dimension, where I use a variable that has stored

IF(MATCH('Names I Want', OriginalNameField))

Type just a variable I created.

I want my pivot table to resemble this:

# Worked %
Name Type

John Doe Type1 50 25%

Type2 100 50%

Type3 10 5%

Type4 40 20%

Lucy Lou Type1 100 26.31%

Type2 200 52.63%

Type3 20 5.26%

Type4 60 15.78%

where the '%' is the percentage of the # worked for that specific type, and that analyst, out of the total worked by that analyst

Also, is there a way to sort within these groups too? For example John Doe's data, sorted, by # Worked, would be 100, 50, 40, 10.

I tried doing this with the AGGR() function but could not, for the life of me, get the results I wanted.

Re: Getting % of sub groups in pivot tables using Aggr()

sunny_talwar — Wed, 21 Dec 2016 19:07:12 GMT

May be this:

Sum([# Worked])/Sum(TOTAL <OriginalNameField> [# Worked])

Re: Getting % of sub groups in pivot tables using Aggr()

lucasdavis500 — Wed, 21 Dec 2016 19:59:13 GMT

Hi Sunny - thanks for the reply

Somethng like this?

=COUNT({<[someDummy] = {'0'}, anotherDummy = {'1'}, DateType = {'Created'}, lastDummy = {'1'}>} Pkey)
/
COUNT(TOTAL <ResolvedBy> {<[someDummy] = {'0'}, anotherDummy = {'1'}, DateType = {'Created'}, lastDummy = {'1'}>} Pkey)

Re: Getting % of sub groups in pivot tables using Aggr()

sunny_talwar — Wed, 21 Dec 2016 20:00:17 GMT

Yes

topic Re: Getting % of sub groups in pivot tables using Aggr() in QlikView

Getting % of sub groups in pivot tables using Aggr()

Re: Getting % of sub groups in pivot tables using Aggr()

Re: Getting % of sub groups in pivot tables using Aggr()

Re: Getting % of sub groups in pivot tables using Aggr()