By default dates in Tableau fit into a standard date hierarchy. Year – Quarter – Month – Day is the default. What happens if you want to change the standard Tableau date hierarchy? For example perhaps you also want the Week included. Fortunately it is simple to build a custom date hierarchy in Tableau.
Recently I had a client request to drill into their data from Year – Month – Day AND Year – Week – Day. Both of these differ to the default date hierarchy in Tableau.
Adding Week into a date hierarchy adds a degree of complexity. This is because weeks generally don’t span quarters and months in a neat way. For example only on rare occasions is the first of the month also the first day of the week. Similarly the last day of the month is rarely the last day of the week. Almost always the end of month A and the beginning of the next month will be in the same week. In other words some weeks will span 2 months.
Therefore the custom date hierarchies need building. Firstly the Year – Week – Day hierarchy. In a previous post I described how to build a hierarchy.
Create the Year – Week – Day hierarchy
Making use of calculated fields we can build a Year – Week – Day date hierarchy.
The need the following values for the hierarchy:
The below example uses the sample Tableau Superstores data. The Order Date is the date field.
Create the following calculated fields:
[Year]: YEAR([Order Date])
[Week]: DATEPART('week',[Order Date])
[Day]: DAY([Order Date])
Convert the fields to dimensions (they default to measures as the result returned is an integer) and build the hierarchy. Call the hierarchy Year – Week and order it correctly; the Order Year first, then Order Week and finally Order Day.
Put the [Year – Week] hierarchy to the Columns shelf and Sales to the Rows shelf. To test the hierarchy click the + on the Order Year pill and the Week should appear. The + sign indicates it’s a hierarchy. Once the hierarchy is expanded, use the – (minus) sign to drill back up.
Create the Year – Month – Day hierarchy
For the Year – Month – Day hierarchy the basic steps are the same as above. Create a new calculated field for Month:
[Month]: MONTH([Order Date])
Also duplicate the existing Year and Day fields. This is because a dimension can only be in one hierarchy; the two hierarchies can’t share fields. Using the new fields create a new hierarchy for the Year – Month.
Combining week and month into one hierarchy doesn’t work. See below what happens when combining Year – Month – Week – Day into a single hierarchy. Sometimes the week number crosses more than one month.
Therefore the hierarchies have to be separate. However this doesn’t mean we need two different hierarchies. We can be smarter and keep one hierarchy, but making the hierarchy flexible. Give the user the power to switch in and out the Week and Month fields.
Create the combined Year – Week / Month – Day hierarchy
Combining a parameter and calculated field gives the user the flexibility to switch between time periods.
Create a String parameter and set it up as a List with two options for the user, Week or Month. Show this parameter control on the worksheet.
Next create a calculated field using the parameter.
[WeekOrMonth]: IIF([Month or Week]= 'Week',[Week],[Month])
When the users selects Week in the parameter it uses the [Week] field. Similarly when the user selects Month it users the [Month] field.
Edit the Year-Week hierarchy and replace [Week] with this new [WeekOrMonth] field.
Now when drilling down through the dates either the week or the month is displayed. It depends on the parameter selection.
Finally tidy it up
I prefer when the Month shows the name instead of the number. For ease of reading I will prefix the week numbers with the word ‘Week’.
To do this edit the WeekOrMonth calculated field. Change the formula to be :
[WeekOrMonth]: IIF([Month or Week]= 'Week','Week ' + STR(DATEPART('week',[Order Date])),DATENAME('month',[Order Date]))
The final result is as below: