topic Re: Pivot Table % in QlikView

Pivot Table %

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

Hello,

I have an expression where I am calculating count of total population for particular group/count of total population for particular company

ex:

count of Group A, company 123/ count of company 123

I'm suppose to get % as individual but i'm always getting 100% which is not true.

I used below exp

Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count({<Company={123}>}DISTINCT Number)

In text box it is giving me correct % but in pivot it is giving me 100% for Group A.

Tried different exp as well but not working

Company is Dim 1 and Group is Dim 2

Company has 3 values inside and Group has 4 to 5 values

If I remove Group Dim, the % comes correct.

Thanks.

stalwar1‌

swuehl‌

Re: Pivot Table %

tripatirao — Tue, 16 May 2017 14:33:17 GMT

Dear Aehman,

Please try with this.

Count(Aggr(Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count({<Company={123}>}DISTINCT Number),

Dim1,Dim2))

Regards

Tripati

Re: Pivot Table %

MK9885 — Tue, 16 May 2017 14:43:56 GMT

Hi Tripati,

thanks for your logic but it is still giving me 100% for row A

I'm not sure if my data model is the problem.

Cus Group or Company is being derived from a composite key to link to fact.

Re: Pivot Table %

vishsaggi — Tue, 16 May 2017 14:55:11 GMT

Can you share a sample to look into ?

Re: Pivot Table %

MK9885 — Tue, 16 May 2017 15:03:50 GMT

Vishwarath, unfortunately I cannot.

I can't even create sample for this, as the table is derived from key fields

Re: Pivot Table %

vishsaggi — Tue, 16 May 2017 15:11:31 GMT

I mean to say your .qvw file which you are working on. You can scramble data and send us the file. Check below.

Preparing examples for Upload - Reduction and Data Scrambling

Re: Pivot Table %

MK9885 — Tue, 16 May 2017 15:25:41 GMT

Ok, the data volume is very high..
I'll make a new qvw with required fields, scramble it and upload here soon.

Thanks

Re: Pivot Table %

c_gilbert — Tue, 16 May 2017 15:25:53 GMT

You need to ignore any group selections from your denominator:

Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count({<Group= ,Company={123}>}DISTINCT Number)

Re: Pivot Table %

neelamsaroha157 — Tue, 16 May 2017 15:42:57 GMT

Try Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count(TOTAL{<Company={123}>}DISTINCT Number)

Re: Pivot Table %

MK9885 — Tue, 16 May 2017 17:55:40 GMT

Thanks all of guys for help...

I used

@Neelam Sehrawat, I used your expression and also Charlotte's expression to get my result....

I had tweak your expression to adjust my requirement. Cus as I said I am calculating for each row

below is my expression

Count({<Group={'A'},Company>}DISTINCT Number)/Count(TOTAL{<Company=,Group={'A'}>}DISTINCT EMPLOYEEIDNBR) +

Count({<Group={'B'},Company>}DISTINCT Number)/Count(TOTAL{<Company=,Group={'B'}>}DISTINCT EMPLOYEEIDNBR) +

Count({<Group={'C'},Company>}DISTINCT Number)/Count(TOTAL{<Company=,Group={'C'}>}DISTINCT EMPLOYEEIDNBR)