Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

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

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

How about this

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

View solution in original post

6 Replies
pradosh_thakur
Master II
Master II

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.



Learning never stops.
mrthomasshelby
Creator III
Creator III
Author

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!

sunny_talwar

How about this

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

mrthomasshelby
Creator III
Creator III
Author

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!

sunny_talwar

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
Creator III
Creator III
Author

That makes sense. Thanks a ton!