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

# 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

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

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

Directory;

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:

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:

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)

RESIDENT data

;

• ###### Re: time scale calculation

date_temp:

min(floor(Arrive)) as mindate,

max(floor(Discharge)) as maxdate

RESIDENT data

Try changing the Directory;  to  data:

• ###### time scale calculation

no its a pitty but this doesn t work either.

but i  agree, it must be something which has to do with the loading of the data

as it works  very well for the example with the inline load statement

• ###### 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.

• ###### time scale calculation

might be usefull but how can i alias my dimension? is this something i have to do in the script?

• ###### Re: time scale calculation

In dimensions you can add lable to the calculated dimensions. Ideally it should be done at script level.

Regards,

Kiran.

• ###### Re: time scale calculation

Did this get resolved as I'm having a similar problem using the script Marc has posted.

Jim