Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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:

View solution in original post

10 Replies
Anonymous
Not applicable
Author

Did you try something like:

     Count(Distinct EmployeeNumber)

?

Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Not applicable
Author

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

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

Regards,

Kiran.

Not applicable
Author

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

;

Anonymous
Not applicable
Author

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:

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Regards,

Kiran.