What is the calculation for the field on the Text shelf? Do you happen to have a packaged workbook you can share (even with mockup data)?
Stanley, I work with punch data and schedule data every day in Tableau, so have got to know some tricks.
I'm assuming that each of those rows you are showing are effectively rows in the database? Or multiple rows (i.e. punch on one row, schedule on another?)
Thanks for both of your replies, I've attached a workbook with some mochup data. I've taken the report as far as I can; I just need to count the occurrences of compliant/non-compliant per user and display that information, preferably in a stacked bar graph.
If you want to take this one, go for it! Otherwise, please let me know and I'll take a look.
No problem. working on it - you have a very similar problem that I have all the time - multiple punches during a single shift. The reason you cannot just use a sum([number of records]) is because your min([punch]) stops working.
There are a number of ways to deal with this. Some Q's:
- Do shifts ever run over midnight?
- How many rows of data are we talking?
- What is the source of the data (excel, SQL)?
Thanks Joshua - it makes sense. I dream about missed punches...
Also, where do you want to to from here - is this a one-off, will you be building other metrics?
That's funny! I look forward to seeing your solution!
To answer your questions:
- No, shifts do not run over midnight
- So far, the data volume is maybe a couple thousand records for a month's worth of data; pretty small
- Source is a CSV file (not ideal); it's a data dump from Five9's and to my knowledge, Tableau doesn't connect directly to that service
I don't plan on using this data for anything other than compliance/non-compliance charting; just getting it to a chart would be helpful.
Understood, can you explain the criteria for a non compliant? At the moment you add a minute to the schedule, then if the first punch is before that time, it's compliant - in other words, as long as they are not more than a minute late, it's compliant?
And based on that, presumably you want to filter out the punch blocks that are after a break, otherwise the non-compliant count will be too high?
A punch is considered non-compliant when the first punch of the day is one minute after or later than their expected start time. A punch is compliant when it's before one minute after their scheduled start time. We only care about the first punch for the day, so no breaks are tracked. Make sense?
So here's the approach I took. Create a Shift ID so that all punches for a day fall under one identifier. Because your shifts never go over midnight, this is simply user number plus date (no time).
Create a punch block that increments for each punch block of the shift. I can use index() here. index() is a table calculation that basically provides a rank. Because of this I must set the partitioning (the index must reset every shift ID) and the order (increment starting with the first punch)
I do that through right clicking the pill, edit table calculation, advanced. However you use this in the future, shift ID must be the only thing in the left box, and the sort order must be set as shown. Every time you use this elsewhere, you have to set this up.
Then we can use this to filter - punch block 1 will always be the first of the day. I also simplified your compliant calc:
IF [Login TimeStamp Converted]<dateadd('minute',1,[Scheduled Start DateTime]) THEN
Please don't hesitate to ask any other questions
I'm not sure what this accomplished; am I able to count the number of compliant/non-compliant occurrences from your example? I couldn't figure out a way to do it and then display that in a bar graph. I've been researching other methods to get the same results and the custom SQL editor seems to be the most promising, however I can't do the SQL aggregations that would help me the most. Here's the custom SQL I'm trying to implement:
SELECT [audience_care_member_start_time#csv].[AGENT FIRST NAME] AS [AGENT FIRST NAME],
[audience_care_member_start_time#csv].[AGENT FULL NAME] AS [AGENT FULL NAME],
[audience_care_member_start_time#csv].[AGENT LAST NAME] AS [AGENT LAST NAME],
[audience_care_member_start_time#csv].[DATE] AS [DATE],
[audience_care_member_start_time#csv].[LOGIN TIMESTAMP] AS [LOGIN TIMESTAMP],
[audience_care_member_start_time#csv].[STATE] AS [STATE],
MIN([audience_care_member_start_time#csv].[TIME]) AS [TIME],
[Agent number assignments - PRIVATE#csv].[Agent #] AS [Agent number assignments - PRIVATE#csv_Agent #],
[Agent number assignments - PRIVATE#csv].[Agent Name] AS [Agent number assignments - PRIVATE#csv_Agent Name],
[Agent number assignments - PRIVATE#csv].[AgentName Upper] AS [Agent number assignments - PRIVATE#csv_AgentName Upper],
[Agent number assignments - PRIVATE#csv].[Start Time] AS [Agent number assignments - PRIVATE#csv_Start Time]
INNER JOIN [Agent number assignments - PRIVATE#csv] ON [audience_care_member_start_time#csv].[AGENT FULL NAME] = [Agent number assignments - PRIVATE#csv].[AgentName Upper]
GROUP BY [audience_care_member_start_time#csv].[DATE]
ORDER BY [audience_care_member_start_time#csv].[AGENT LAST NAME],[audience_care_member_start_time#csv].[DATE],[audience_care_member_start_time#csv].[TIME]
The part that breaks it is the MIN() function on the TIME column; it returns an error like can't recognize [AGENT FULL NAME] or something. This JOIN's data from two CSV files right now, so would that be preventing me from using the MIN() aggregation on the data? If I can just have the SQL choose the minimum time based on the day, I'm good and can build the charts I need.