Recommended way to count working people at set time slots

[#1]

Hi,

I like to be able to count the numbers of persons working at spec time slots a day but I'm not sure whats the best way to handle the problem as most of my attempts feels clunky or wrong.

I have a table as shown below and I would like to be able to plot the number of people working ex at 10:00, 11:00 and 12:00 based on their start and end time.

ID	Start	End
1	08:00	17:00
2	09:00	23:00
3	07:15	15:30
4	10:00	12:00
1	19:00	23:10

So, any recommendation how to address this issue?

[#2]

How about using

Class(Start,1) as the dimension

and Count(distinct ID) as the expression?

Or you can just do a floor on your time field and calculate the hour for each time. E.g 8:32am would become 8am

[Colin-Albert]

This technical brief has some examples that could be relevant to youGenerating Missing Data In QlikView

The example in the PDF for 'Creating a record for each day that a contract is valid'  is similar to your requirement, you just need to convert the time interval from days to hours.

[#4]

I have not looked into the class function for this, but i will give it a shoot to see if It might give me what Im looking for.

I dont have a date field at the table today (I can add one if required, no problem) but I can't see how that solves my problem at this time as the problem is not to get the time but to count the hours that at set between the start and end time.

[MarcoWedel · Accepted solution]

Hi,

one possible solution could be:

tabWorkHours:
LOAD *
FROM [http://community.qlik.com/thread/149043] (html, codepage is 1252, embedded labels, table is @1);

tabTime:
LOAD Time((RecNo()-1)/24,'hh:mm') as Time
AutoGenerate 24;

tabLink:
IntervalMatch(Time)
LOAD Start, End Resident tabWorkHours;

hope this helps

regards

Marco

[#6]

Thank you Colin,

I have looked at the example in the PDF and it works wonderfully when i use date but I cant get it to work with the time format I use in my starting post (hh:mm). also not sure what function to use instead or date (or if I should use date with formation flag).

Any idea?

[#7]

Hi and thank you Marco,

That is def an idea to try out and similar to what I had in mind when I started looking at the task and might be the way I choose. i will give it a go and see how it plays out with some more live data.

Thank you!

[Colin-Albert]

Hi Hampus,

Is your time field a text value e.g. 07:30 or is it a Qlikview time field created using time() or time#()?

If you add a test chart showing yourtimefield and num(yourtimefield) you can test this.

[#9]

In my test i just did was it just a inline field that was right aligned so I assume that it seen as a num field. However, have not double checked so its interpreted to correct time field.