10 Replies Latest reply: Feb 17, 2014 5:06 AM by Jim McHale RSS

    time scale calculation

    Marc van Eijk

      I have a table with three colums

      colum one is named  start time

      colum two is named end time

      colum three is named employee number

       

      now i want to know how many employees where in the office at different times  between 08.00 and 17.00

      therefore i use the calculated dimension:

      .

      =timestamp(class(frac(Tijd_Start) ,30/(24*60)),'hh:mm')

       

      question is which expresion do I have to use

        • time scale calculation
          Dennis Hoogenboom

          Did you try something like:

           

               Count(Distinct EmployeeNumber)

           

          ?

            • time scale calculation
              Marc van Eijk

              Thanks for the reply and  yes i did ,

              but with only the count expression it is calculating the employees available at the start-time and not the time between start and end time.

               

              example: if two employees have a start time at 07.30 and one is left at 09.30 and the other at 12.00

              the outcome should be like this:

               

              Starttime: # employees

              8h     2

              9h     2

              10h   1

              11h   1

              12h    0

                • time scale calculation
                  Dennis Hoogenboom

                  I see your problem.

                  Is there for you a way to create a flag for every hour that the employee is in?

                  I think you should do that in your loading script that way you can count those flags as an expression.

                    • time scale calculation
                      Marc van Eijk

                      Yes i think that is a good idea I;ve read a solution for almost the same issue (Hourly Census graph) and

                      they did something with creating a calander table in combination with intervalmatch#  function.

                       

                      I copied this script but is not working as I have to load the data from a file where the expample script is working with an inline table for the data.

                       

                      my script now is looking like this but gives an error on the field  Date

                       

                      Directory;

                      LOAD Patient,

                          timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,

                                timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge,

                                timestamp(floor(Arrive) + Maketime(hour(Arrive)), 'MM/DD/YYYY hh') as ArriveHour ,

                           Unit

                      FROM

                      data.xlsx

                      (ooxml, embedded labels, table is data);

                       

                       

                      date_temp:

                      LOAD

                                min(floor(Arrive)) as mindate,

                                max(floor(Discharge)) as maxdate

                      RESIDENT data

                      ;

                      LET vMindate = peek('mindate')-1;

                      LET vMaxdate = peek('maxdate');

                      DROP TABLE date_temp;

                       

                       

                      // Generate a calendar table with one row for each hour each date

                      FOR i = 0 to 23                    // Hour 0 to 23

                                Calendar:

                                LOAD *,

                                timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,

                                $(i) as Hour

                                ;

                                LOAD

                                          date($(vMindate) + IterNo()) as Date

                                          AUTOGENERATE 1

                                          WHILE $(vMindate) + IterNo() <= $(vMaxdate)

                                ;

                      NEXT i

                       

                       

                      // IntervalMatch to connect Patient visits to the Calendar table

                      IntervalMatch (DateHour)

                      LOAD ArriveHour, Discharge

                      RESIDENT data

                      ;

                    • time scale calculation

                      Alias your dimension to the WorkHour. Create the expression like:

                       

                      Count({<[start time]={"<=WorkHour"},[end time]={">WorkHour+1"}>} Distinct EmployeeNumber)

                       

                       

                      Regards,

                      Kiran.