Problem with COUNT

[#1]

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?

[sujeetsingh]

Pz do give a sample!

[hic]

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

[#4]

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.

[hic]

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

[#6]

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?

[hic · Accepted solution]

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

[#8]

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

[hic]

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

HIC

[#10]

I got it to work with this expression:

AVG

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

Thanks for the help!