This may explain it better,
you can see from this sample data that user JOS was picking between 8 am and 9 am, but it is not represented correctly in the chart from the original post, you can see it's green for picking but only for the start time until the start time of the next task, but I need it colored from 8 to 9, like the below mock-up
I hope that clears up what I am trying to do
Thomas is certainly right. That is perhaps the same problematic as a master calendar, but with time in a day.
Many times, the problematic is that we get an event between two dates, and this event must be "applied" to a single date (how many clients, contracts do I have a given day). Therefore, I would say:
1) create a dimension with a given time elapse : every 15 or 30 minutes
2) relate each event to this dimension (you will duplicate some lines)
3) do the chart (I did not understand the different colors, so, will you need also to split the numbers ?)
Something along the following:
Time(IntMinute/24/60) as Minute;
StartTime_IntMin + IterNo()-1 as IntMinute
While IterNo() <= FinishTime_IntMin - StartTime_IntMin + 1 ;
Floor(24*60*[Start Time]) as StartTime_IntMin,
Floor(24*60*[Finish Time]) as FinishTime_IntMin
From xxx ;
where all the fields containing 'IntMin' are integer minutes.
You would need additional code to handle intervals spanning over midnight though...
Assuming StrtTime and EndTime are valid time stamps:
LOAD ID, Start,
Timestamp(Start+IterNo()/(60*24), 'hh:mm') as NewStart,
While Start+IterNo()/(60*24) <= EndTime
IterNo() is a function starting at 1. You can IterNo()-1 to start at 0.
A day is between 0 and 1: 0,5 is 12:00. Therefore, 1 minute is 1/60*24
The loop is done until we reach the upper bound (here End Time)
I'm recommending this article by just reading the subject line. And also read Henric's post:
and one more very useful post