topic Re: Problem with COUNT in QlikView

Problem with COUNT

Wed, 30 Jan 2013 11:50:43 GMT

Hello,

Im having a problem related to this topic: http://www.qlikcommunity.com/thread/71272?tstart=0

Im using a master calendar with the date field 'CalendarDate'. Calendardate is linked to the columns DATIZIEK (Day of getting sick) and DATEZIEK(date of getting better, wich are related to ID_ZKT (Also with ID_PER, but let take this one).

If i do the following in my chart for count(CalendarDate) it counts the days an employee was sick. But here is the problem, lets look at the following example:

ID_ZKT DATIZIEK DATEZIEK

1 10-1-2013 15-1-2013

2 10-1-2013 15-1-2013

When i do count(CalendarDate), selected on the 10-1-2013, its returning the value 4 and not the value 2 what is should be. For some reason it counts for the first ID 2 days for 10-1-2013 and also for the second. If just one ID_ZKT is related to a DATIZIEK and DATEZIEK value or the DATEZIEK value is different, then it returns the right number of days.

Someone an idea how to solve this?

Re: Problem with COUNT

sujeetsingh — Wed, 30 Jan 2013 12:08:16 GMT

Pz do give a sample!

Re: Problem with COUNT

hic — Wed, 30 Jan 2013 13:20:35 GMT

I suspect you have multiple entries in your master calendar. Try loading this with the distinct clause.

If it still doesn't work, an example would be good.

HIC

Re: Problem with COUNT

Wed, 30 Jan 2013 13:22:42 GMT

Hi,

Here is an example. I have selected the date 10-1-2013 and as you can see the chart returns the value 4, and not the value 2 (10-1-2013 contains 2 sick days from ID_PER 1 and 2). If the sick and better days of 2 ID_PER are not the same it reurns the right value.

Re: Problem with COUNT

hic — Wed, 30 Jan 2013 13:32:59 GMT

The if-function inside the count causes this.

Since the Count() depends on two fields, ID_ZKT and CalendarWeekDay, all combinations between these are created.

Further, you have a data model with a synthetic table that potentially can mess things up.

HIC

Re: Problem with COUNT

Wed, 30 Jan 2013 13:59:01 GMT

Thanks for your response. But what is the expression supposed to be then? Just count(CalendarDate) or count(CalendarWeekDay) doesnt work.

Or is there something else what i must do?

Re: Problem with COUNT

hic — Wed, 30 Jan 2013 14:31:46 GMT

The joins in the script caused duplicates. As did the intervalmatch. And the if inside the count also caused duplicates.

See attachment for how I would do it. The script is changed so that you no longer have an unnecessary synthetic table. I also simplified it somewhat.

HIC

Re: Problem with COUNT

Wed, 30 Jan 2013 15:35:48 GMT

Thanks but im using the personal edition so i cant open the qvw file.

Re: Problem with COUNT

hic — Wed, 30 Jan 2013 16:10:33 GMT

Try again. I have replaced the file with one containing an embedded license.

HIC

Re: Problem with COUNT

Thu, 31 Jan 2013 10:12:07 GMT

I got it to work with this expression:

AVG

(aggr(count(distinct [ID_ZKT]),CalendarDate))

Thanks for the help!