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

# Late clockin percentage

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?

Thanks!

• ###### 1. Re: Late clockin percentage

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

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.

Right now, it just appears as above.

• ###### 3. Re: Late clockin percentage

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

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

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

then

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

should give you your percentage

• ###### 6. Re: Late clockin percentage

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.