topic Count( all<... together with calculated dimensions does not behave as I expect in QlikView

Count( all<... together with calculated dimensions does not behave as I expect

Fri, 18 Dec 2009 06:08:56 GMT

All records in my database contain a field CODE. And each record belong to a sales Organization (SO_VKORG). I would like to show a table where I see the top CODES (in percentage of their share in one sales organization). Top CODES are all above a share of e.g. 1% (0.01).

SO_VKORG1

CODE1 30%

CODE2 10%

SO_VKORG2

CODE 3 40%

...

In a table chart I use a normal and a calculated dimension:

=SO_VKORG

=if(aggr(Count(SO_P_VBELN)/count(all<SO_VKORG>SO_P_VBELN),CODE)>0.01,CODE)

in the expression, I want to count the current selection and all lines in the database for a particular VKORG

=Count(SO_P_VBELN) / count(all <SO_VKORG> SO_P_VBELN)

unfortunately, the count all does only count the amount of records which are displayed, not all records of the database. If I increase or decrease the percentage to be displayed, the number of lines displayed (which is correect) and the total amount of counted records change (which is not correct). This leads to wrong results..

I use QV 9 Personal edition.

What is my mistake?

regards

andreas

Count( all<... together with calculated dimensions does not behave as I expect

johnw — Sat, 19 Dec 2009 05:55:27 GMT

This probably won't help, but I believe that the QlikView documentation indicates that they're moving away from using the "all" keyword. Instead, I believe it suggests using "{1} total". So you might try count({1} total ...) and see if it works. I doubt it will, but it can't hurt to try.

Another thing to try would be using CODE as the dimension, and putting the rest of the logic in your expression so that it returns null for anything <=1%. I'd think it would work the same, so I don't see how it would fix the problem, but again, might be worth a try.

Other than that, I'm not sure what to suggest. What you have looks right to me.

Wait, maybe it's the aggregation. Perhaps you need to aggregate by SO_VKORG as well?

if(aggr(Count(SO_P_VBELN)/count(all<SO_VKORG>SO_P_VBELN),SO_VKORG,CODE)>0.01,CODE)

Not sure.

Count( all<... together with calculated dimensions does not behave as I expect

Tue, 22 Dec 2009 15:05:43 GMT

Dear John

thank you for your response. Unfortunately, your proposal does not help. If I implement it, I get 100% for all aggragations. I think, the problem is more on the expressions, not on the dimensions side. I tried the new notation count({1} total ...) instead of all, but with the same result.

andreas

Count( all<... together with calculated dimensions does not behave as I expect

johnw — Wed, 23 Dec 2009 00:25:12 GMT

Is it possible to post a small example with inline loads that demonstrates the problem?