Sum employee sick days excluding the days he was sick in a following year

[#1]

Hello,

I'm new to qlikview (using the personal edition) but im going to try to be clear as possible about my problem. I'm using a table from an oracle database, here a part of it as an example:

ID_PER	ID_WGV	DATESICK	DATEBETTER
240	1	15-12-1998 0:00	3-1-1999 0:00
41	1	31-12-1998 0:00	11-4-1999 0:00
70	1	29-12-1998 0:00	6-1-1999 0:00
224	1	21-12-1998 0:00	3-1-1999 0:00
13	1	30-12-1998 0:00	3-1-1999 0:00

The third column show the day an employee is sick and the fourth column show the day an employee is better (this day counts as a sick day, the employee star working again on the next day). I created a table object with the following colums:

Year

Month

Date

(I loaded these columns as seperate from the DATESICK column. Then i created the following expression SUM (DATEBETTER - DATESICK+ 1), this will get the number of days an employee was sick in a certian period.

The problem here is that sometimes an employee is sick in, for example, 1998 and gets better in 1999.

If i click on the year 1998 in my table object it shows the number of days the employees where sick including the days they were sick in the beginning of 1999. I want that my table object only shows the Sick days of a certain year.

Is this possible and how? I hope that u can understand my problem. If u have question or need more information about my problem just tell me. 🙂

[#2]

I changed the script a bit and its working now. Thanks for your help and quick responses!

I used the following (let niet op de Kolom namen):

T1:
LOAD

DATIZIEK,

    Year (DATIZIEK) as DepYear,

     Month (DATIZIEK) as DepMonth,

     Day (DATIZIEK) as DepDay,

     Time (DATIZIEK) as DepTime,
    

DATEZIEK,

    Year (DATEZIEK) as DepYear1,

     Month (DATEZIEK) as DepMonth1,

     Day (DATEZIEK) as DepDay1,

     Time (DATEZIEK) as DepTime1

;
SQL SELECT *
FROM PRODPIM."PIM_TZKT";

T2:load *, DepYear+IterNo()-1 as YEAR,if(DepYear=DepYear1,DATEZIEK-DATIZIEK+1,
  if(DepYear=DepYear+IterNo()-1,
     YearEnd(DATIZIEK)-DATIZIEK,
     DATEZIEK-YearStart(DATEZIEK)+1)) as YEARSICKTIMEresident T1 while DepYear+IterNo()-1<=DepYear1;

SQL SELECT *
FROM PRODPIM."PIM_TZKT";
drop table T1;