8 Replies Latest reply on Nov 5, 2010 5:23 AM by Alex Kerin

    Find min one row, max in another, do calculations

    Alex Kerin

      My data is structured as shown in the image. There is no chance of that structure changing.

       

      Basically I have timeclock punch in and out information for employees. At the start of the shift, the employee punches-in, hopefully sometime around the scheduled time. They then usually punch out for lunch, back in after lunch, before finally punching out, again hopefully around the indicated schedule out time.

       

      Lunches are not scheduled, and each row is a block of punch in/ punch out time. Therefore a single shift will have two rows of data.

       

      I need to calculate how early someone punches in compared to their schedule, and similarly, if they are punching out late.

       

      I don't know how to structure a calculated field to assess every row of data that pertains to a single shift.

       

      I cannot use time difference from scheduled to weed out the lunch as they could come in very late, and may take a lunch break very close to leaving.

       

      Any ideas?

        • 1. Re: Find min one row, max in another, do calculations
          James Baker

          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.

          • 2. Re: Find min one row, max in another, do calculations
            Alex Kerin

            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. 

            • 3. Re: Find min one row, max in another, do calculations
              Alex Kerin

              Ha, For some instances though I can use CountD(ShiftID) instead of Number of Records.......

              • 4. Re: Find min one row, max in another, do calculations
                James Baker

                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.

                • 5. Re: Find min one row, max in another, do calculations
                  Richard Leeke

                  > 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!):

                   

                  SELECT

                  [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]

                   

                  Maybe.  ;-)

                   

                   

                  In version 6 you could use custom table calculations to get the data from the adjacent rows all available at the ame time.

                   

                  Maybe.  ;-)

                  • 6. Re: Find min one row, max in another, do calculations
                    Joe Mako

                    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

                    _AVG
                    to
                    _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

                    • 7. Re: Find min one row, max in another, do calculations
                      Alex Kerin

                      Guys, just starting my workday - can't thank you enough for your efforts. Coffee and begin to digest what you've suggested...

                      • 8. Re: Find min one row, max in another, do calculations
                        Alex Kerin

                        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.