It sounds like this post's title has the right idea. Graph person by day, and a calc of MIN([timeclock]) and MAX([timeclock]) will give the values you're looking for. You can then write a max - min calc, or whatever you want to calculate?
The key point here regarding "assess every row of data that pertains to a single shift" is that partitioning your viz on Employee and Day levels would seem to give you the sets of shift-specific rows that you can then evaluate your calculations against.
So i created a shift ID (Employee+Scheduled In) - this allows me to do max and min when I have this dimension in one of the shelves. It's still messy though as now I get all of the 20,000 rows of shifts that are in my data set, rather than summarized values.
Don't know if there's any way around this though without changing the data source.
Ideally I would go up against the pseudo-data set that's created with one row per Shift ID.
Ha, For some instances though I can use CountD(ShiftID) instead of Number of Records.......
I don't really know what you're doing, and so can't offer much further advice.
One possible solution to your "rather than summarized values" problem, though, is to create a RUNNING_* table calc and then filter (using another calc with INDEX) to only showing the last (sum of everything) value.
> Ideally I would go up against the pseudo-data set that's created with one row per Shift ID.
A couple of ideas off the top of my head:
In version 5.2, if you're not averse to Custom SQL you could create yourself a query which returned one row per shift ID. Something like (i.e. I just wrote this and it may not work!):
[First].[Employee] AS [Employee],
[First].[In Punch] AS [First_In],
[First].[Out Punch] AS [First_Out],
[Second].[In Punch] AS [Second_In],
[Second].[Out Punch] AS [Second_Out],
[First].[Scheduled In] AS [Scheduled In],
[First].[Scheduled Out] AS [Scheduled out]
FROM [Employee_Time] AS [First]
INNER JOIN [Employee_Time] AS [Second]
ON [First].[Employee] = [Second].[Employee]
AND [First].[Scheduled In] = [Second].[Scheduled In]
AND [Second].[In Punch] > [First].[Out Punch]
In version 6 you could use custom table calculations to get the data from the adjacent rows all available at the ame time.
Attached is a Tableau 6 workbook with a way to look at this data.
I added another two rows to the data source, also attached, as an example of an employee that takes no lunch break, but leaves early.
I did two sets of calculations, one at the Shift ID (combination of Employee and shift date) level, and one at the Employee level.
The sheet at the Shift ID level can be done in Tableau 5.2, but the Employee level uses custom Table Calculations.
I added a parameter
[Lunch Minutes], that can be set as a constant in Tableau 5, or you could merge in alloted lunch length with a calculated field, eg, case statement, or another data source.
The set of calculations performed are:
- Minutes Late
(difference between first In Punch and Scheduled In)
(MIN([In Punch])-MIN([Scheduled In]))*1440
- Minutes Left Early
(difference between last Out Punch and Scheduled Out)
(MAX([Scheduled Out])-MAX([Out Punch]))*1440
- Minutes Short
(difference between Punched time and Scheduled time)
((AVG([Scheduled Length])-SUM([Punch Length]))*1440)-[Lunch Minutes]
- Minutes at Lunch
(difference between first Out Punch and last In Punch, or 0 if no lunch taken)
IIF(MAX([In Punch])-MIN([Out Punch])<0,0,(MAX([In Punch])-MIN([Out Punch]))*1440)
The value 1440 converts the time length form a fraction of a day to minutes.
These will work when the mark level of detail, aggregation level, is at the Employee Shift ID and in the worksheet "Row for Each Employee Shift ID".
If you want to view the Average Minutes across all shifts at the Employee level, you can use a custom Table Calc like:
IF INDEX()=1 THEN WINDOW_AVG([Minutes Late]) END
with the "Default Table Calculation" "Compute using" set to the Employee Shift ID field.
The IF statement calculates this once per level of detail (in this case once per Employee)
If you wanted a different level, say overall average, you would just remove the "Employee" field from the worksheet.
If you wanted the sum of minutes instead of the average, just change
_SUM, or whatever aggregate you want.
(Thanks to Richard Leeke for point out that my previous use of limiting to the first index can be done as an IF statement instead of on the Filter shelf)
(Thanks to Dimitri Blyumin for for helping me see how partitioning is affected by more than the options set in the Edit Table Calc dialogs)
Overall, I think this situation is a fantastic example of Table Calculations, because it can show:
- how they work, in this case, an example of an aggregate of an aggregate
- how they are reusable, change pills on sheet, table calc adjusts partition
- and the formulas readable without too many notes needed to explain why or what is happening in the formula
Guys, just starting my workday - can't thank you enough for your efforts. Coffee and begin to digest what you've suggested...
Ok, got it all. The two key things are the window averages - I've never used table calcs like this, but see how you define the 'window' using the Shift ID. The INDEX is also key - making sure we don't double dip (or triple dip if they have another break).
Joe, Richard, James, thank you again for your time spent on this. I hope others will begin to understand the power of the table calculations.