Please see if the attached could be a starting point for you.
I followed kettan 's method described here:
I made some small adjustments to your dates
(combining the date with the time).
I then used the following calculation to get the hours per slot:
IF [StartShift]<[Hour End] AND [EndShift]>[Hour Start]
IF [StartShift]>DATEADD('minute',1,[Hour Start])
THEN (DATEDIFF('minute',[Hour Start],[StartShift])-1)/60
ELSEIF [EndShift]<DATEADD('minute',1,[Hour End])
THEN (DATEDIFF('minute',[Hour Start],[EndShift])-1)/60
There was some weirdness about having to adjust by a minute in
different parts to get it to work. Probably there is a cleaner way to do this.
Thanks swaroop.gantela, you rock! After duplicating what you had, I made just a few changes:
- I used calculated fields to join the Date and Start Time to give me the Start Shift, and I added the duration to that to give me the proper End Shift. This will allow me to more easily update the shift data in the future.
- When I tested on some additional data, I found a few issues when someone had more than one shift start and end in the same hour (if they had a break), so I tweaked the HourInSlot calculation a little and it works great.
Thanks again for the assist!
Staff Heat Map.twbx 1,015.3 KB
Looks nice! Glad it worked out.
I saw in mine too that there were some 0.000 values floating around.
Wasn't sure why. So I put "HourInSlot" on the Filter shelf, and set the
lower value to be something small, like 0.1, and that seemed to get
rid of it.