10 Replies Latest reply: Dec 31, 2012 7:42 AM by dbuijsman RSS

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

      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_PERID_WGVDATESICKDATEBETTER
      240115-12-1998 0:003-1-1999 0:00
      41131-12-1998 0:0011-4-1999 0:00
      70129-12-1998 0:006-1-1999 0:00
      224121-12-1998 0:003-1-1999 0:00
      13130-12-1998 0:003-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. :-)

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

            Thanks for your reaction but i can't open the QVX file because im using the free personal edition of qlikview.

              • Re: Sum employee sick days excluding the days he was sick in a following year
                Gysbert Wassenaar

                Dang, I read that before getting started and thought that a smart thing to mention. And then I post a qvw file anyway... duh!

                 

                Anyway, try the script below. You'll get a YEARSICKTIME and a YEAR field so that you can sum the sicktime per year.

                 

                T1:
                LOAD
                ID_PER, ID_WGV,
                date#(DATESICK,'DD-MM-YYYY hh:mm') as DATESICK,
                date#(DATEBETTER,'DD-MM-YYYY hh:mm') as DATEBETTER,
                year(date#(DATESICK,'DD-MM-YYYY hh:mm')) as YEARSICK,
                year(date#(DATEBETTER,'DD-MM-YYYY hh:mm')) as YEARBETTER
                  INLINE [
                    ID_PER, ID_WGV, DATESICK, DATEBETTER
                    54, 1, 01-12-1998 00:00, 04-12-1998 00:00
                    46, 1, 03-12-1998 00:00, 06-12-1998 00:00
                    156, 1, 05-12-1998 00:00, 25-12-1998 00:00
                    65, 1, 12-12-1998 00:00, 16-12-1998 00:00
                    41, 1, 14-12-1998 00:00, 23-12-1998 00:00
                    240, 1, 15-12-1998 00:00, 03-01-1999 00:00
                    41, 1, 31-12-1998 00:00, 11-04-1999 00:00
                    70, 1, 29-12-1998 00:00, 06-01-1999 00:00
                    224, 1, 21-12-1998 00:00, 03-01-1999 00:00
                    13, 1, 30-12-1998 00:00, 03-01-1999 00:00
                    85, 1, 02-01-1999 00:00, 05-01-1999 00:00
                    134, 1, 05-01-1999 00:00, 12-01-1999 00:00
                    241, 1, 06-01-1999 00:00, 09-01-1999 00:00
                    12, 1, 11-01-1999 00:00, 01-02-1999 00:00
                ];

                T2:
                load *,
                YEARSICK+IterNo()-1 as YEAR,
                if(YEARSICK=YEARBETTER,DATEBETTER-DATESICK+1,
                  if(YEARSICK=YEARSICK+IterNo()-1,
                     YearEnd(DATESICK)-DATESICK,
                     DATEBETTER-YearStart(DATEBETTER)+1)) as YEARSICKTIME
                resident T1 while YEARSICK+IterNo()-1<=YEARBETTER;

                drop table T1;

                 

                 

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

              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;