
Re: Percentages exceeding 100% when dividing by Count Total Distinct
pradosh thakur Sep 21, 2017 6:39 AM (in response to Phalgun P)Distinct ID Count is the count(ID) ?
use aggr function with sum to get the total of count
something similar to
Count(distinct(ID)) > 7451
aggr(Count(distinct total <[Month]> (ID)),Month) to get the temp data then sum it.

Re: Percentages exceeding 100% when dividing by Count Total Distinct
Phalgun P Sep 21, 2017 7:03 AM (in response to pradosh thakur)Hello. Thanks for the reply. So I used this expression:
Count(distinct(ID))/Sum(aggr(Count(distinct total <[Month]> (ID)),Month)). But it's still not giving me the correct output. Infact I'm getting everything as zeroes now. Can you point out if I'm doing anything wrong?TIA!


Re: Percentages exceeding 100% when dividing by Count Total Distinct
Sunny Talwar Sep 21, 2017 7:09 AM (in response to Phalgun P)How about this
Count(DISTINCT ID)/Sum(TOTAL <Month> Aggr(Count(DISTINCT ID), Month, Vendor)) * 100

Re: Percentages exceeding 100% when dividing by Count Total Distinct
Phalgun P Sep 21, 2017 7:12 AM (in response to Sunny Talwar )As always you are spot on! Can you give me a brief explanation of how the expression in the denominator works if you don't mind? Thanks a lot!

Re: Percentages exceeding 100% when dividing by Count Total Distinct
Sunny Talwar Sep 21, 2017 7:14 AM (in response to Phalgun P)Without the Aggr function, the DISTINCT ID was giving you the total DISTINCT ID for the Month (in other words if an id repeated within a single month for multiple vendors, it was just used once for the denominator). When we introduced Aggr(), it found the distinct ID per Month per vendor and then summed it

Re: Percentages exceeding 100% when dividing by Count Total Distinct
Phalgun P Sep 21, 2017 7:24 AM (in response to Sunny Talwar )That makes sense. Thanks a ton!


