Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Khan_Mohammed
Honored Contributor II

Pivot Table %

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

1 Solution

Accepted Solutions
neelamsaroha157
Valued Contributor II

Re: Pivot Table %

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

9 Replies
tripatirao
Contributor II

Re: Pivot Table %

Dear Aehman,

Please try with this.

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

Dim1,Dim2))


Regards

Tripati

Khan_Mohammed
Honored Contributor II

Re: Pivot Table %

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
Esteemed Contributor III

Re: Pivot Table %

Can you share a sample to look into ?

Khan_Mohammed
Honored Contributor II

Re: Pivot Table %

Vishwarath, unfortunately I cannot.

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

vishsaggi
Esteemed Contributor III

Re: Pivot Table %

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

Khan_Mohammed
Honored Contributor II

Re: Pivot Table %

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
Contributor II

Re: Pivot Table %

You need to ignore any group selections from your denominator:


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

neelamsaroha157
Valued Contributor II

Re: Pivot Table %

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

Khan_Mohammed
Honored Contributor II

Re: Pivot Table %

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)