Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Your script says:
date_temp:
LOAD
min(floor(Arrive)) as mindate,
max(floor(Discharge)) as maxdate
RESIDENT data
But your loaded table doesn't have the name data.
Try changing the Directory; to data:
Did you try something like:
Count(Distinct EmployeeNumber)
?
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
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.
Alias your dimension to the WorkHour. Create the expression like:
Count({<[start time]={"<=WorkHour"},[end time]={">WorkHour+1"}>} Distinct EmployeeNumber)
Regards,
Kiran.
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
;
Your script says:
date_temp:
LOAD
min(floor(Arrive)) as mindate,
max(floor(Discharge)) as maxdate
RESIDENT data
But your loaded table doesn't have the name data.
Try changing the Directory; to data:
might be usefull but how can i alias my dimension? is this something i have to do in the script?
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
In dimensions you can add lable to the calculated dimensions. Ideally it should be done at script level.
Regards,
Kiran.