Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mrthomasshelby
Contributor

Percentages exceeding 100% when dividing by Count Total Distinct

Hello, I have a data set as follows:

Month

MonthVendorDistinct ID Count
JanA7451
B6260
C563
FebA7260
B6250
C560
MarA7128
B6266
C540
AprA7138
B6285
C559

Now I want to display the percentages of the distinct IDs per vendor for each month. For example: In January, Vendor A percentage is

7451/(7451+6260+563)*100=52.199%. I'm using the following expression:

Count(distinct(ID))/Count(distinct total <[Month]> (ID))*100

But this expression is giving me the percentages as 62,52,5 sum of which is exceeding 100%. Can you please help me get my expression right? TIA!

stalwar1

1 Solution

Accepted Solutions
MVP
MVP

Re: Percentages exceeding 100% when dividing by Count Total Distinct

How about this

Count(DISTINCT ID)/Sum(TOTAL <Month> Aggr(Count(DISTINCT ID), Month, Vendor)) * 100

6 Replies
pradosh_thakur
Valued Contributor III

Re: Percentages exceeding 100% when dividing by Count Total Distinct

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.



mrthomasshelby
Contributor

Re: Percentages exceeding 100% when dividing by Count Total Distinct

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!

MVP
MVP

Re: Percentages exceeding 100% when dividing by Count Total Distinct

How about this

Count(DISTINCT ID)/Sum(TOTAL <Month> Aggr(Count(DISTINCT ID), Month, Vendor)) * 100

mrthomasshelby
Contributor

Re: Percentages exceeding 100% when dividing by Count Total Distinct

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!

MVP
MVP

Re: Percentages exceeding 100% when dividing by Count Total Distinct

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

mrthomasshelby
Contributor

Re: Percentages exceeding 100% when dividing by Count Total Distinct

That makes sense. Thanks a ton!

Community Browser