topic Re: Dual + Count <> Count ?! in QlikView

Dual + Count <> Count ?!

pedromsouza — Wed, 01 Nov 2017 22:01:08 GMT

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

Re: Dual + Count <> Count ?!

swuehl — Wed, 01 Nov 2017 22:51:14 GMT

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)

Re: Dual + Count <> Count ?!

pedromsouza — Wed, 01 Nov 2017 23:22:30 GMT

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

Re: Dual + Count <> Count ?!

swuehl — Thu, 02 Nov 2017 08:04:13 GMT

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?

Re: Dual + Count <> Count ?!

pedromsouza — Thu, 02 Nov 2017 08:30:55 GMT

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.

Re: Dual + Count <> Count ?!

swuehl — Thu, 02 Nov 2017 08:52:45 GMT

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?

Re: Dual + Count <> Count ?!

swuehl — Thu, 02 Nov 2017 08:59:06 GMT

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.

Re: Dual + Count <> Count ?!

pedromsouza — Thu, 02 Nov 2017 14:11:12 GMT

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))

Re: Dual + Count <> Count ?!

vishsaggi — Thu, 02 Nov 2017 14:16:21 GMT

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

Re: Dual + Count <> Count ?!

swuehl — Thu, 02 Nov 2017 18:38:53 GMT

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

=Count( Aggr( osm_pac, osm_pac))

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

Re: Dual + Count <> Count ?!

pedromsouza — Thu, 02 Nov 2017 21:40:19 GMT

Good to know. Thanks!

Re: Dual + Count <> Count ?!

pedromsouza — Thu, 02 Nov 2017 21:41:03 GMT

That's correct! That's exactly what I wanted.

Thank you very much.