Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
pedromsouza
Creator II
Creator II

Dual + Count <> Count ?!

Hi,

I was looking for an expression that would give me number and percentage in a pie chart and found this:

=dual( Num(Count(Distinct key),'#.##0') & '   (' & num((Count(Distinct key))/(Count(Total Distinct key)),'#.##0%') & ')',

Count(Distinct key) +(Count(Distinct key))/(Count(Total Distinct key))*100 )

It shows number and % correctly, but the count is wrong.

If I use the previous expression:

=Count(Distinct key)

I get a smaller value.

So, what's I'm doing wrong?

Regards,

Pedro.

Edit: the smaller value is the correct one.

Mensagem editada por: Pedro Souza

Qliking since '09
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Your osm_pac - Grupo relation is a n:m relation, i.e. an osm_pac can show multiple Grupo values.

That's why your total distinct count of osm_pac does not match the sum of the distinct count per Grupo dimension.

Not sure how the Aggr() function should help you here.

Can you elaborate how you want to match the pie chart values with your total distinct count?

View solution in original post

11 Replies
swuehl
MVP
MVP

Why are you using

Count(Distinct key) +(Count(Distinct key))/(Count(Total Distinct key))*100


as the numeric part of your dual value (i.e. the second argument to the function) and not


Count(DISTINCT key)


?

pedromsouza
Creator II
Creator II
Author

Hi Stefan,

I don't why. I think I didn't understand the dual function and tried to copy exactly the example I had.

I changed it to:

=dual( Num(Count(Distinct key),'#.##0') & '  (' & num((Count(Distinct key))/(Count(Total Distinct key)),'#.##0%') & ')',

Count(Distinct key) /(Count(Total Distinct key))*100 )

But the result is the same.


Thanks

Qliking since '09
swuehl
MVP
MVP

The result shouldn't be the same as compared to your first expression.

The second expression should hold the percentage value in the numeric representation.

Could you post a small sample QVW or some screenshots and your expected result to show the exact issue?

pedromsouza
Creator II
Creator II
Author

Stefan,

while I created this example I figure it out. I get different results because I'm counting distinct values for dimension and I tried to compare it with distinct values "for all".

Maybe with Aggr I can create a count for dimension.

Qliking since '09
swuehl
MVP
MVP

Your osm_pac - Grupo relation is a n:m relation, i.e. an osm_pac can show multiple Grupo values.

That's why your total distinct count of osm_pac does not match the sum of the distinct count per Grupo dimension.

Not sure how the Aggr() function should help you here.

Can you elaborate how you want to match the pie chart values with your total distinct count?

swuehl
MVP
MVP

Maybe try

=Count( Aggr( osm_pac, osm_pac))

This will assign the osm_pac to the Grupo where it first appears in LOAD order, so an osm_pac is only counted once in the chart.

Not sure if this makes sense in terms of your requirements, though.

pedromsouza
Creator II
Creator II
Author

Using Aggr() didn't work, but at least now I can explain what's happening. Thank you.

Tryied Count(Aggr(Distinct osm_pac,osm_pac)) and Count(Aggr(Distinct osm_pac,Grupo))

Qliking since '09
vishsaggi
Champion III
Champion III

Hello Pedro,

Just a small info. Aggr() implicitly uses DISTINCT so even if you don't mention distinct in your Aggr() it should work fine. If you do not want DISTINCT you have to Explicitly use NODISTINCT keyword.

Thanks

V.

swuehl
MVP
MVP

This is what I see in your sample QVW if I use as expression

=Count( Aggr( osm_pac, osm_pac))

2017-11-02 19_35_04-QlikView x64 - [Radar ].png

The sum of the rows / slices adds up to 4158 which seems to be the value you are after, right?