topic Re: Count query in QlikView

Count query

robert99 — Wed, 23 Apr 2014 15:48:26 GMT

I've wondered why QV is set up in this way

If I have three tables

Table 1 has ten calls. CallNum 101 to 110

Table 2 has these ten calls plus a FSRNum (say 2 each) number for each = 20 CallNum/FSRNum combinations

Table 3 has these 10 calls plus 2 partnumbers for each CallNum and FSRNum = 40 Call/FSRNum/PartNum combinations

If I put a expression in as Count (CallNum)

If I just have the dimension of CallNum the expression gives a total of 10

if I have the dimension of CallNum and FSRNum the expression gives a total of 20. (as it should be)

if I have the dimension of CallNum and FSRNum and PartNumber the expression gives a total of 20 (rather than 40)

Why is this? Its not a big (or even a small issue) issue but it seem to defy logic. Surely Count (CallNum) should count the total number of calls shown. And Count distinct just the distinct calls

I can Count (PartNum) to get the total of Parts so its not an issue but ..

Or is there a setting to change this somewhere

Re: Count query

robert99 — Wed, 23 Apr 2014 15:57:18 GMT

I can get the total as either

Count (PartNum)

Sum(1)

Depending on whether Calls without parts should be included in the count or not

Re: Count query

antoniotiman — Wed, 23 Apr 2014 16:04:27 GMT

Hi,

try

Count({<FSRNum=,PartNum=>} CallNum)

Regards

Re: Count query

TKendrick20 — Wed, 23 Apr 2014 16:11:03 GMT

Does this happen to be in a bar chart? I've been running into a similar issue because the "Total" in your example with three Dimensions is only taking into account the last segment. For instance, only counting PartNumber for the last FSRNum dimension. Don't know if that helps particularly, but you can kind of see what the logic is by turning on "Plot Values Inside Segments" if it does happen to be a bar chart.

Re: Count query

robert99 — Wed, 23 Apr 2014 17:30:17 GMT

I think I understand the logic now

QV in effect converts table three into CallNum & FSRNum (and removes the separate CallNum and FSRNum in the table) when the synthetic join is done.

So table 1 has CallNum

Table 2 has CallNum and FSRNum and CallNum & FSRNum

Table 3 has in effect just CallNum & FSRNum and PartNum

So the total number of CallNum is 20 not 40

To count all three tables

count (CallNum & FSRNum & PartNum) to include CallNum with no part

count (PartNum) exclude CallNum with no PartNum

etc

Re: Count query

TKendrick20 — Wed, 23 Apr 2014 18:01:37 GMT

Interesting... that had me baffled a few days ago until I figured out a workaround.

Re: Count query

robert99 — Wed, 23 Apr 2014 18:36:52 GMT

It gave the same result as count (CallNum)

I surprised something that seems so simple is quite tricky. But by putting in the fields to count (with distinct when required) gives the correct result regardless of the dimensions used. This seems to be the safest option

CallNum 100 FSRNum 1 PartNum ABC

CallNum 100 FSRNum 2 PartNum ABC

CallNum 100 FSRNum 3 PartNum ABD

Expression

count (CallNum & PartNum)

count (CallNum & FSRNum & PartNum)

count (distinct CallNum & FSRNum & PartNum)

ALL EQUAL 3

count (distinct CallNum & PartNum) EQUALS 2

COUNT (DISTINCT CallNum) = 1

and after reading this I might start using count distinct now to reduce the risk of error

http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/22/a-myth-about-countdistinct