KNOWLEDGE BASE

Dynamically Group Dates by Month Level in Most Recent Quarter or by Quarter Level

Published: 19 Sep 2017

Question

How to dynamically group dates by month level in most recent quarter or by quarter level.  For example, the final view may show October, November, and December of 2014, as well as Q1, Q2 and Q3 of 2014.

Environment

Tableau Desktop 9 and later versions

Create and name a calculated field as "Date Groups" and enter a formula similar to the following:
IF DATETRUNC( 'quarter', { FIXED : MAX( [Date] ) } ) = DATETRUNC( 'quarter', [Date] )
THEN DATENAME( 'month', [Date] ) + " " + STR( YEAR( [Date] ))
ELSE "Qtr " + STR( DATEPART( 'quarter', [Date] )) + " " + STR( YEAR( [Date] ))
END

In the above calculation, if the first day of the quarter that contains the most recent date in the data set is the same as the first day of the quarter of [Date] then the calculation returns the name of the month and the year as a string.

If the first conditional statement is not true then the calculated field will return the quarter that the date belongs to.