6 Replies Latest reply on Mar 16, 2016 9:29 AM by Scott Benner

    Late clockin percentage

    Brian Winkle

      I'm trying to figure out how to calculate the percentage of late clock-ins. My table includes [Scheduled Start] and [Clock In] and a "late clockin" is defined as a clockin that occurs more than 10 minutes after it was scheduled to start.


      I created a calculated field to determine whether a shift was late or not:

      IF DATEDIFF('minute', [Clock In], [Scheduled Start]) > 10 THEN "Late" ELSE "On-time" END


      But now I'm curious how to chart the percentage of "Late" clock-ins vs the total number of clock-ins. Any advice?



        • 1. Re: Late clockin percentage
          Satish Kikani

          you can use stacked bar graph to show it easily.


          If you can provide metadata, I can try to build one and show you.

          • 2. Re: Late clockin percentage
            Brian Winkle

            Satish, I'm not sure what you mean by metadata in this case. I'm presuming I need to perform some sort of aggregation on the calculated field, but I'm not sure how to do that.


            Screen Shot 2016-03-16 at 10.01.53 AM.png


            Right now, it just appears as above.

            • 3. Re: Late clockin percentage
              Satish Kikani

              metadata means some dummy data in excel so that i can create a workbook on top of that.


              or if you can share your twbx workbook. it wil lbe great.


              yes, you have to do % of total by Table down or across should work fine. Depends on the layout.

              • 4. Re: Late clockin percentage
                Brian Winkle

                So, I've changed the calculated field above to a new field I'm calling [Late Clockin]:

                IF DATEDIFF('minute', [Clock In], [Start Time]) > 10 THEN "Late" END


                I'm then creating another field called Late Percentage that does this:

                COUNT([Late Clockin]) / COUNT([Clock In])


                Does this seem correct? I'd rather not create a bunch of dummy data if the solution is simple/

                • 5. Re: Late clockin percentage
                  Chris Dickson

                  could be simpler still assuming 1 row per clock in in the data then


                  Create calc field called [Late] as

                  IF DATEDIFF('minute', [Clock In], [Start Time]) > 10 THEN 1 END



                  sum(Late)/sum([number of records])


                  should give you your percentage

                  • 6. Re: Late clockin percentage
                    Scott Benner

                    From what you described, your Data Source is 1 record per clock in event (per Person?), including some kind of [Shift] identifier, containing at least [Clock In] as DateTime and [Scheduled Start] as DateTime.   You have an additional calculated column [On Time] as String that returns "Late" or "On-time"


                    What you need to do is use a Quick Table Calculation:

                    1) Put your [On Time] in the Rows like you have done above

                    2) Drag [Number of Records] to the [Text] marks box.   This should convert to an aggregate SUM(Number of Records), and you should visualize the total number of clock-ins grouped into Late and On-time, regardless of shift or time period.

                    3) Right-click the SUM(Number of Records) pill.  Find Quick Table Calculation > Percent of Total.  You should now % of all records, broken into Late and On-time,

                    4) Drag your [Shift] to the Columns, and it will now display % late grouped by Shift.  You can do similar groupings if you want to look at Monthly groupings (drag Scheduled Start, as a Month, Week or Day).  Or mix and match more groupings. 


                    Now, if you want to flip the visualization to [Shift] on the Rows and [On Time] to Columns, you must also flip the direction of aggregation.  Right-click the SUM(Number of Records) pill, and change the Compute using from Table (Down) to Table (Across).  Using Analysis > Swap Rows and Columns will do this for you automatically.