Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count(TOTAL{<Company={123}>}DISTINCT Number)
Dear Aehman,
Please try with this.
Count(Aggr(Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count({<Company={123}>}DISTINCT Number),
Dim1,Dim2))
Regards
Tripati
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.
Can you share a sample to look into ?
Vishwarath, unfortunately I cannot.
I can't even create sample for this, as the table is derived from key fields
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
Ok, the data volume is very high..
I'll make a new qvw with required fields, scramble it and upload here soon.
Thanks
You need to ignore any group selections from your denominator:
Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count({<Group= ,Company={123}>}DISTINCT Number)
Try Count({<Group={'A'},Company={123}>}DISTINCT Number)/Count(TOTAL{<Company={123}>}DISTINCT Number)
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)