Get total of days by year and month but having overlapping periods

[#1]

Hello,

Im having a problem to sum sick days of employees by month and year. Many sick periods are overlapping more then one month or year.
What i want is the total of sick days by year and month (maybe days) but cant do this easily cause of the overlap. Im using an oracle database, here is a part of it:

ID_PER	ID_WGV	DATIZIEK	DATEZIEK
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

And i have the following script:

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 YEARSICKTIME
resident T1 while DepYear+IterNo()-1<=DepYear1;

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

With this solution i can show YEAR and SUM(YEARSICKTIME) to show the number of days the employees where sick by year. But now i want to zoom in by month.

So for example someone is sick at 28-11-12 and better at 02-12-12, the month november gets 3 days and decembet gets 2 days by there total.

Does someone know how i can make this work? (Maybe with an more simple solution that i have now)

[Gysbert_Wassenaar]

You can use the intervalmatch function. See attached qvw. I've also used the Qlikview Components library to create a master calendar. That's optional though, but highly recommended. It makes it so much easier to work with dates.

[#3]

Thanks for your quick response. I Cant look in the qvw file because of the use of the personal edition.

As you said i created a Master Calendar like this:

LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today());
 
TempCalendar: LOAD
  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
 
MasterCalendar: LOAD
    TempDate AS CalendarDate,
    Day(TempDate) AS CalendarDay,
    WeekDay(TempDate) AS CalendarWeekDay,
    Week(TempDate) AS CalendarWeek,
    Month(TempDate) AS CalendarMonth,
    Year(TempDate) AS CalendarYear,
    'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
    WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear RESIDENT TempCalendar ORDER BY TempDate ASC;
  DROP TABLE TempCalendar;
  LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today());

It works and i have a calendar now, but i cant figure out how to show the SUM of sick days by year, month and day. Any ideas?

[Gysbert_Wassenaar · Accepted solution]

You probably still need to do the intervalmatch dance.

...your mastercalendar stuff here...

// load your sick days data in a temp table

TempSickData:

LOAD ID_PER,

     ID_WGV,

     date(DATIZIEK,'DD-MM-YYYY') as DATIZIEK,

     date(DATEZIEK,'DD-MM-YYYY') as DATEZIEK

FROM ...sourcetable...;

// create a new table SickDate, start with the data from the mastercalendar

SickData:

noconcatenate load * Resident MasterCalendar;

//use intervalmatch to match the dates from the calendar with

//the inteval dates from the sick days table

inner join(SickData) IntervalMatch(CalendarDate) load DATIZIEK, DATEZIEK Resident TempSickData;

//join the rest of the fields from TempSickDate with the new SickData table

inner join load * Resident TempSickData;

// drop the temp table

drop table TempSickData;

[#5]

I did what u said and im having the following script now:

LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today());
OLEDB CONNECT TO [Provider=MSDAORA;User ID=prodpim;Data Source=ppimat] (XPassword is QcJUbRVMODdEXUNMZC);
 
TempCalendar: LOAD
  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
 
MasterCalendar: LOAD
    TempDate AS CalendarDate,
    Day(TempDate) AS CalendarDay,
    WeekDay(TempDate) AS CalendarWeekDay,
    Week(TempDate) AS CalendarWeek,
    Month(TempDate) AS CalendarMonth,
    Year(TempDate) AS CalendarYear,
    'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
    WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear RESIDENT TempCalendar ORDER BY TempDate ASC;
  DROP TABLE TempCalendar;
  LET vDateMin = Num(MakeDate(1998,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today());



TempSickData:
LOAD ID_WGW,

     ID_PER,

     date(DATIZIEK,'DD-MM-YYYY') as DATIZIEK,

     date(DATEZIEK,'DD-MM-YYYY') as DATEZIEK;
SQL SELECT *
FROM PRODPIM."PIM_TZKT";

SickData:

noconcatenate load * Resident MasterCalendar;
inner join(SickData) IntervalMatch(CalendarDate) load DATIZIEK, DATEZIEK Resident

TempSickData; inner join load * Resident TempSickData;
drop table TempSickData;

All the fields are loaded. But what do i need to do now in my chart expressions? A simple SUM(DATEZIEK - DATIZIEK +1) doesnt work. Any ideas? 

[#6]

Its working now, thanks for the help!