Dual + Count <> Count ?!

[pedromsouza]

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

[swuehl]

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]

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

[swuehl]

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]

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.

[swuehl · Accepted solution]

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]

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]

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

[vishsaggi]

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]

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?