What are date functions?
Date functions allow us to manipulate dates in a data source.
Blue vs. Green Dates
It is important to review how dates work in Tableau. When dragging order date to columns, it automatically defaults to a blue pill and the year of order date. In Tableau, dates follow a hierarchical structure. Clicking on a date field in Tableau unveils another date field, but at a more detailed level of granularity.
Blue Dates
If quarter is removed, 4 years of data is displayed at the month level, which consists of 48 individual bars and 48 headers (found at the bottom of the bar chart). When the pill is blue and when headers are displayed, that is a sign that you are working with a discrete date.
Green Dates
If month of order date is changed from a discrete date to a continuous date, the date pill changes from blue to green, and the individual headers are replaced with a continuous axis. So when a chart has a green date, and an axis, then it is known that chart consists of continuous dates. Continuous dates give you an axis
Gaps in Time
Even though we know that blue dates are usually discrete and green dates are usually continuous, the above bar charts still appear to be fairly similar. When choosing Colorado after adding the State/Province filter, the bar chart looks a little different. Not only are there are months with sales on the continuous axis, there are also months with no sales, which are represented by a gap, or an absence of a bar. Therefore, a continuous axis visualize sales and no sales for all the months over 1 continuous period of time.
However, if month of order date is changed again to discrete date, the view changes. Since the continuous axis has been replaced with individual headers again, only months with sales are included in the view, while months without sales are not shown.
Date Functions
DATETRUNC
DATETRUNC truncates or shortens a date to specified time interval. When looking at the date of 2023-10-16, it remains unchanged without the use of DATETRUNC.
When using DATETRUNC the truncate a date to the month level, it rolls all the days up to the first day for month, while the year and month remained in changed.
When using DATETRUNC to truncate the date to the year level, both month and day have been rolled all the way up to the first month and day, respectively, while the year remains unchanged.
DATETRUNC Use Case: Sales Over Time
Perhaps data comes in on daily level but viewing data on a monthly level is more useful. This can be the case when tracking sales over time. When viewing sales over time a on a daily level, it can provide a lot of detail about daily performance, but it can be difficult to identify overall trends.
However, when sales are truncated to the month level, it is easier to that sales are roughly increasing over time in this continuous line chart.
DATEPART
DATEPART will display one part of the date, and each date part will be represented with numerical headers. If DATEPART is used to look at date on the month level, DATEPART will consolidate all data to just the month level without displaying the day or the year.
DATEPART Use Case: The month with the highest sales
When the DATEPART function is used, it displays 12 bars for the 12 months, and shows that month 11 has had the highest sales.
Additionally, if year of order date is dragged onto color, then it is comprised of all 4 years.
DATENAME
DATENAME will display one part of the date, and each date part will be represented with string headers (e.g., a name, a word).
DATENAME Use Case: The weekday with the fewest sales
DATEDIFF
DATEDIFF will show the passage of time between 2 dates. The passage of time can be measured in different units (e.g., days, months, etc).
DATEDIFF Use Case #1: Identify which warehouses are taking longer to ship
When looking days between Order Date and Ship Date for the 3-Ring Staple Pack, the DATEDIFF function revealed that the difference between order date and ship date is 6.
DATEDIFF Use Case #2: Identify which warehouses are taking longer to ship
DATEADD
DATEADD adds or subtracts time to a chosen date using different time measurements (i.e., days, months, etc.).
DATEADD Use Case: Subtract 12 months from the current month.