KNOWLEDGE BASE

Incorrect Results When Grouping Times Past Midnight in the Previous Day


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

Issue

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.

Environment

Tableau Desktop

Resolution

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

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