In this recipe, we will create a custom date period that will drive the labels and detail shown in our time series graph.
To follow this recipe, open B05527_03 - STARTER.twbx. Use the worksheet called Custom Date Period, and connect to the DOHMH New York City Restaurant Inspection Results data source.
The following are the steps to create a chart with a custom date period parameter:
- Right-click the arrow beside the Dimensions section in the sidebar, and select Create Parameter.
- Create a string parameter called
Select Period to Comparewith the following settings:
- Show the parameter control for
Select Period to Compare. You can do this by right-clicking on the parameter, and selectingShow Parameter Control. - Create a calculated field called
Periodwith the following formula. Don't worry if this is a little bit long, we will explain what each block does in the following How it works section.
- Create another calculated field called
Custom Period Labelfor the axis labels. The formula is as follows:
- From Dimensions, drag the calculated field Period to the Filter shelf, and exclude Null. You can do this by checking the Exclude option, and then checking Null. Notice that the selected Null gets a strikethrough, meaning it is being excluded.
- From Dimensions, drag Inspection Date to the Filters card and choose Years.
- Exclude 1900 from the list of the years, and click OK when done.
- From Dimensions, drag the calculated field Custom Period Label to the Columns shelf.
- From Measures, drag the Number of Records field to Rows.
- From Dimensions, drag the calculated field Period to Color in the Marks card.
- Right-click on the Custom Period Label header, and choose to Hide Field Labels for Columns.
- Right-click on the Number of Records axis, and select Edit Axis.
- Change the axis title to # Inspections.
- For each of the parameter selection, edit the colors in the color legend so that all "Current" periods are assigned one color, and all "Previous" periods are assigned a different color.
- Test the parameter. Confirm that when the parameter choice changes, the detail of the time series graph and the corresponding labels change.
Tableau allows for relative date filters out of the box. When you drag a date field onto the Filters shelf, you can choose Relative Dates. The following window allows you to choose the configuration of relative dates.
Tableau, however, does not have the built in capability to allow you to compare periods, like this month vs last month, or current year-to-date (YTD) vs previous year's YTD.
In this recipe, we worked around this limitation by using parameters and calculated fields. First, we created a parameter that allows us to specify which periods we want to make available.
The parameter choice will drive the calculated field used to filter the dates being displayed in the view, as well as the labels for the dates.
In this recipe, we have kept the comparison consistent – the current date range compared to the previous date range. For a Year over Year (YOY) comparison, we consider anything that happened in the current and the previous year. The block of code will separate what happened in the current year, in the previous year, and in other years.
If the inspection happened earlier than the previous year, those records will be assigned a Null label, which we will exclude in the view.
If we are looking for Current Year-to-Date (YTD) over Previous YTD, meaning we only want inspections that happened until the current month and day for both years, the following block restricts the comparison to the current month and day.
The DATEADD function allows us to add or subtract time intervals to a given date. If we provide a positive value for the second argument, then we are adding the time interval. If we provide a negative value, we are technically subtracting the time interval.
If we are comparing quarters, that is, current quarter over previous quarter, the formula becomes a little bit tricky. We cannot simply check for current quarter number compared to current quarter number minus 1. We need to consider that, if the current quarter is the first quarter, if we simply subtracted 1, we are going to be looking for quarter 0, which will be incorrect. If the current quarter is 1, we need to compare the current quarter with the previous year's fourth quarter. This logic is handled in the following block of code.
The Month over Month comparison, that is, comparing the current month with the previous month, shares the same challenge as Quarter over Quarter. We cannot simply compare current month number to current month number minus one. If we are currently on month number one, or January, we should compare it to last year's month twelve, or December. The following block of code handles this logic.
The other tricky part in this recipe is the labels for the period. We wanted to make sure that the previous period and current period line up. This is not an issue when we want to compare Year over Year, or current YTD over previous YTD, because we can simply display the months.
The labels become more of an issue when we are looking at Quarter over Quarter (QoQ) or Month over Month (MoM). We cannot use months when we are dealing with QoQ, because the months in the current quarter will not line up with the months from the previous quarter in a time series graph. For quarters, we can simply display the month number in that quarter.
This is what the graph will look like when Quarter over Quarter is chosen:
When we are looking at Month over Month (MoM), we also cannot just look at months, as this will produce just two dots – one for each month – that don't line up. We have to display the graph based on the days in the month, so that the points still line up. This is what the graph will look like when Month over Month is chosen:
If you need to display custom date periods in your Tableau dashboards, you can use the techniques presented in this recipe as a reference or starting point. You can expand the selection, or integrate additional logic into your graphs using parameters and calculated fields.
Please refer to the recipe in Appendix A, Calculated Fields Primer.