Incorrect Results When Grouping Times Past Midnight in the Previous Day

Published: 03 Oct 2016
Last Modified Date: 25 Aug 2017


When attempting to aggregate values for times that span midnight, calculations that are grouped at the day level may show incorrect results. 

For example, the daily sum of sales for the night shift may be incorrect if some individuals clock in before midnight and others after midnight.


Tableau Desktop


Create a modified date field that groups times after midnight as the previous day by creating a new calculated field:
IF [Shift] = "night"
AND DATEPART('hour',[Clock-in Time]) >= 0
AND DATEPART('hour',[Clock-in Time]) <= 6
THEN DATEADD('day',-1,[Clock-in Time])
ELSE [Clock-in Time]

Update the [Shift] and [Clock-in Time] fields with your own data source fields. 
Did this article resolve the issue?