what best practice joining 'shift' data , other time series data in tableau? working multiple geo data (from la india, uk, ny, malaysia, australia, china etc), , lot of employees work past midnight.
for example, employee has shift @ 9 pm 6 on 2016-07-31. 'report date' 2016-07-31 no time zone information provided.
this employee work , there events (time stamps in utc) between 2016-07-31 21:00 2016-08-01 06:00. when @ events though, 7/31 have events between 21:00 , 23:59. if filter july, calculations skewed (the event data cut off @ midnight though shift extended 6 am).
i need make calculations based upon total time employee engaged work (productive) , total time paid. request daily/weekly/monthly.
if can me out here or give me talking points explain superiors, appreciated. seems must common scenario. need request new raw data format or there can on end?
the shift data looks this:
id date regular_hours overtime_hours total_hours abc 2016-06-17 8 0.52 8.52 abc 2016-06-18 7.64 0.83 8.47 abc 2016-06-19 7.87 0.23 8.1 the event data more detailed (30 minute interval data on events handled , time took complete events in seconds):
id date interval events event_duration abc 2016-06-17 01:30:00 4 688 abc 2016-06-17 02:00:00 6 924 abc 2016-06-17 02:30:00 10 1320 so, sum event_duration entire day , number of seconds spent doing work. can compare amount of time employee paid see how efficient staffing is.
my concern event data has date , time (utc). payroll data has date without time zone information. causes inaccuracies when blending data in tableau because shifts cross midnight. there way around or need propose new data requirements?
(fyi - people have been calculating based on date years without considering time zones before. assumption did not realize cause inaccurate results)
Comments
Post a Comment