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

    time scale calculation

      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

              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

                      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



                      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 ,




                      (ooxml, embedded labels, table is data);





                                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


                                LOAD *,

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

                                $(i) as Hour



                                          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)