Dynamically Changing the Date Level

Published: 17 Mar 2017
Last Modified Date: 20 Jul 2023

Question

How to dynamically change the view based on a specified date level. For example, show sales data by year, month or day.

Environment

Tableau Desktop

Answer

The above steps can be reviewed in the attached workbook dynamically change date level.twbx.

CLICK TO EXPAND STEPS

Step 1: Create a parameter to use as the date level selector

Select Dimension drop-down, and then select Create Parameter.
In the Create Parameter dialog box, do the following:
- Give the parameter a name. For this example, use Date Level Selector.
- For Data Type, select String.
- For Allowable values, select List.
- For List of values, enter year, month, and day, then click OK.
In the Data window, right-click Date Level Selector and choose Show Parameter Control.

CLICK TO EXPAND STEPS

Step 2: Create a calculated field

Create a calculated field that displays the data based on the date level specified by the date level selector parameter.

Select Analysis > Create Calculated Field.
Name the field Date Selector, enter the following calculation, then click OK:
DATE(DATETRUNC([Date Level Selector], [Order Date]))

CLICK TO EXPAND STEPS

Step 3: Create the view

Drag Date Selector to Columns.
On the Columns shelf, right-click the Date Selector field, and then select Exact Date and Discrete.
Drag Sales to Rows.

Additional Information

Note that the calculation returns a full date truncated to the chosen date level and it will not be formatted. For example, selecting 'month' will return 1/1/2017, not 'January 2017'.

There is no way to dynamically format a field based on a parameter selection. If this kind of formatting is necessary, a more complicated calculation can be written that pulls the date apart and returns the desired pieces in the desired order as concatenated strings. The string version of these dates will no longer be sorted in the expected way, by default. Right-click the string pill in the view to 'Sort > by Field > [Order Date] (original date field) > min/max aggregation' to apply a 'date' sort.

See the 'Date Level w/ Formatting' worksheet in the attached workbook for an example of how this would work. The following calculation has been provided as an example:

[Date Selector w/ Formatting]

IF [Date Level Selector]='year' then STR(YEAR([Order Date]))
ELSEIF [Date Level Selector]='month' then  DATENAME('month',[Order Date]) +', '+
                                        STR(YEAR([Order Date]))
ELSEIF [Date Level Selector]='day' then DATENAME('month',[Order Date]) +' '+
                                    STR(DAY([Order Date]))+', '+
                                    STR(YEAR([Order Date]))
END