Pivot Table %

[MK9885]

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‌

[tripatirao]

Dear Aehman,

Please try with this.

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

Dim1,Dim2))

Regards

Tripati

[MK9885]

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.

[vishsaggi]

Can you share a sample to look into ?

[MK9885]

Vishwarath, unfortunately I cannot.

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

[vishsaggi]

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

[MK9885]

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

Thanks

[c_gilbert]

You need to ignore any group selections from your denominator:

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

[neelamsaroha157 · Accepted solution]

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

[MK9885]

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)