Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
ruanhaese
Partner - Creator II
Partner - Creator II

Count total distinct

Hi everyone

Im working on some data that looks like this:

DimA, DimB, Amount

Blue, 1,  100

Blue, 2,  100

Blue, 3,  100

Green, 1, 100

Green, 2, 100

Count(Distinct DimB) results in barchart as

Blue, 3

Green, 2

And the formulae COUNT(DISTINCT TOTAL DimB) results in 3

If I try and get the right relative value for each dimension, so that

Blue = 3 / 5 = 60%

Green = 2 / 5 = 40%

But the formulae COUNT(DISTINCT DimB) / COUNT(DISTINCT TOTAL DimB)  results in =>

Blue 3/3 = 100%

Green 2/3 = 66%

I.e. the 3 / 5 should refer to the total sum of the rows.

In a bar chart how can I get the right relative value so that its based on the sum of the rows and not the count distinct?

Thank you

1 Solution

Accepted Solutions
ToniKautto
Employee
Employee

This should work.

Sum(TOTAL Aggr(COUNT(DISTINCT DimB), DimA))

2017-11-13 21_04_02-Qlik Sense Desktop.png

View solution in original post

7 Replies
ruanhaese
Partner - Creator II
Partner - Creator II
Author

Tried this as well but came up with the wrong values

=SUM(TOTAL Aggr( Count(Distinct DimB), DimA ))

shraddha_g
Partner - Master III
Partner - Master III

COUNT(DISTINCT TOTAL DimB) will give you 3

COUNT( TOTAL DimB) will give you 5

Try

COUNT(DISTINCT DimB) / COUNT(TOTAL DimB)

ToniKautto
Employee
Employee

That wouldn't give the distinct count in the denominator. I would think the Aggr() might be the easiest way.

Quy_Nguyen
Specialist
Specialist

Hi Ruan,

As i can see  there are 3 distinct values of DimB on your data.

And this expression: COUNT(DISTINCT TOTAL DimB)  will return 3.

If you want the result is 5, try this: COUNT(TOTAL DimB)


Best.

ToniKautto
Employee
Employee

This should work.

Sum(TOTAL Aggr(COUNT(DISTINCT DimB), DimA))

2017-11-13 21_04_02-Qlik Sense Desktop.png

ruanhaese
Partner - Creator II
Partner - Creator II
Author

Thanks, I agree it makes sense,

but as I mentioned earlier it returns the wrong value.

Edit: I played around with the data I provided and this solution

did return the correct values, so I'll mark it as correct.

Not quite sure why it didn't apply to my data though.

Thanks for the help.

Will play around with the AGGR function more.

gio92
Contributor II
Contributor II

Can you please provide the solution to this problem? I'm tryng to achieve the same result and have the same problem.

Thanks