In the #WOW2022 Week 26 Challenge, Sean Miller tells us that he loves using Tableau’s relative date filters.

Relative date filters allow us to define date ranges either:

  • Are updated based on the date and time the view is opened (Today).
    • Relative date filters make it easy to create views that always display the most recent data. For example, you can check the sales of the last 30 days or the sales of the year of the date or those that were closed last week; either
  • Or that are calculated according to a given specific fixed date (also known as anchor date).

Sean also tells us that “one thing that has always bothered him is that the anchor date is not dynamic: neither the end user nor the data update will change the anchor date”.

This is because an anchor date can only be changed by editing the filter from the filter shelf, as the filter displayed in Viz does not allow the anchor date to be changed.

The challenge of the week consists precisely in building a relative date filter where the user is allowed to update the anchor date that determines the date range, in addition the user will be able to select the level of grouping in which the dates are presented in the principal chart.

I clarify that my final design has some variations against Sean’s design, since when implementing the solution, I couldn’t help but try and include some additional conditions.

Here is, the steps I followed:

CREATE THE DIFFERENT MENUS OF CONFIGURATION OPTIONS

STEP 1. MENU TO SELECT THE TIME UNIT IN WHICH THE DATE WILL BE DISPLAYED IN THE VIEW [TIME UNIT VIEW (TUV)].

The user can select one of the following options: Annual, Quarterly, Monthly, Weekly and Daily. To create the menu view, you have at least two options:

  • Create a new data source with the information of the different options that allow us to create the view.
  • Establish an Internal Data Densification, which consists of creating a new field with the required values, from an existing field in the original data source.

In this exercise, I used the second option by creating from the “Order Date” field, a field with the five necessary values.

TUV ScaffoldINT([Order Date]) % 5 + 1
TUV MenuCASE [TUF Scaffold]
WHEN 1 THEN “Daily”
WHEN 2 THEN “Weekly”
WHEN 3 THEN “Monthly”
WHEN 4 THEN “Quarterly”
WHEN 5 THEN “Annual”
END

To store the option selected by the user, a parameter will be defined.

Identify the option that is currently selected.

TUV Color[TUV Scaffold] = [P.Date Precisión View]

STEP 2. MENU TO SELECT TIME UNIT FILTER (TUF).

The user will be able to select one of the options among a maximum of 5 options (Year, quarter, month, week and day).

Although it was not established in the requirements of the challenge, I found it interesting to limit the number of time unit options available for the filter, in such a way that only the options where the filter time unit is greater than or equal to the unit of time used in the view.

TUF ScaffoldINT([Order Date]) % 5 + 1
TUF MenuCASE [TUF Scaffold]
WHEN 1 THEN “Day”
WHEN 2 THEN “Week”
WHEN 3 THEN “Month”
WHEN 4 THEN “Quarter”
WHEN 5 THEN “Year”
END
TUF Filter[TUF Scaffold] >= [P.Time Unit View]

To store the option selected by the user, a parameter will be defined.

Identify the option that is currently selected.

TUF Color[TUV Scaffold] = [P.Date Precisión View]

STEP 3. MENU TO SELECT THE TYPE OF ANCHOR DATE [ANCHOR TYPE (AT)].

The user will be able to select between today’s date or a specific established date.

AT ScaffoldINT([Order Date]) % 2 + 1
AT MenuCASE [AT Scaffold]
WHEN 1 THEN “Today”
WHEN 2 THEN “Custom Date”
END

Create parameter to store the option selected by the user.

Identify the option that is currently selected. A filled circle will be used for the option that is selected, for the rest of the options an empty circle will be used.

 AT Color & ShapeIIF([AT Scaffold] = [P.Anchor Type], “◉”, “◯”)

Create parameter to capture the custom anchor date.

Calculate the anchor date depending on the type of anchor selected:

Anchor DateIIF([P.Anchor Type] = 1, { MAX([Order Date]) }, [P.Anchor Date])
Anchor Date AdjustDATE(CASE [P.Time Unit Filter]
WHEN 1 THEN [Anchor Date]
WHEN 2 THEN DATETRUNC(“week”, [Anchor Date])
WHEN 3 THEN DATETRUNC(“month”, [Anchor Date])
WHEN 4 THEN DATETRUNC(“quarter”, [Anchor Date])
WHEN 5 THEN DATETRUNC(“year”, [Anchor Date])
END)

Note: In this exercise where the data is fixed, instead of taking the current date, it was replaced by the maximum date recorded in the data, which is 12/30/2022.

STEP 4. CREATE THE PARAMETER WITH THE VALUE OF “N” FOR THE LAST N AND NEXT N OPTIONS.

STEP 5. MENU TO SPECIFY THE TIME PERIOD TO INCLUDE IN THE VIEW [DATE PERIOD FILTER (DPF)].

The user will be able to select depending on the time unit of the filter between 5 and 6 time period options (For the day time unit, only the first 5 options are presented).

The text for each of the options is dynamic based on the filter time unit (UTF) and the anchor date type (Today or a custom date).

Identify the option that is currently selected. A filled circle will be used for the option that is selected, for the rest of the options an empty circle will be used.

DPF Color[DPF Scaffold] = [P.Date Period Filter]
DPF ShapeIIF([DPF Color], “◉”, “◯”)

Create dynamic labels for the options in this menu

DPF ScaffoldINT([Order Date]) % 6 + 1
DPF MenuCASE [DPF Scaffold]

WHEN 1 THEN IIF([P.Time Unit Filter] = 1, “Yesterday”, “Previous ” + [Label of TUF])

WHEN 2 THEN IIF([P.Time Unit Filter] = 1, IIF([P.Anchor Type] = 1, “Today”, “Anchor day”), “This ” + [Label of TUF])

WHEN 3 THEN IIF([P.Time Unit Filter] = 1, “Tomorrow”, “Next ” + [Label of TUF])

WHEN 4 THEN “Last ” + IIF([DPF Color], STR([P.N]), “N”) + ” ” + [Label of TUF] + IIF([P.N] = 1, “”, “s”)

WHEN 5 THEN “Next ” + IIF([DPF Color], STR([P.N]), “N”) + ” ” + [Label of TUF] + IIF([P.N] = 1, “”, “s”)

WHEN 6 THEN IIF([P.Time Unit Filter] = 1, “”, UPPER(LEFT([Label of TUF], 1)) + MID([Label of TUF], 2) + ” to ” + IIF([P.Anchor Type] = 1, “date”, “anchor”))

END

Calculate the column and row to display each option.

ColumnIIF([DPF Scaffold] <= 3, 1, 2)
Row ([DPF Scaffold] – 1) % 3 + 1

STEP 6. ADJUST IF NECESSARY THE MENU OPTIONS WHEN ONE OF THE OPTIONS BECOMES INCONSISTENT.

An example of this situation occurs when time period 6 is selected, with one of its dynamic labels such as: “Year to date” or “Month to anchor” and the user selects the time unit of the filter: Day.

When the user selects the filter time unit at the day level, option 6 of the time period does not exist and therefore I replace it with option 2.

Update DPFIIF([TUF Scaffold] = 1 AND [P.Date Period Filter] = 6, 2, [P.Date Period Filter])

Another example of this situation occurs when changing the time unit of the view, it is greater than the time unit of the filter, in this situation, it will be replaced with the value of the time unit of the view.

Update TUFIIF([TUV Scaffold] > [P.Time Unit Filter], [TUV Scaffold], [P.Time Unit Filter])

Note: These requirements are in addition to the original requirements stated in the challenge.

STEP 7. DEFINE THE VIEWS OF THE MENUS WITH THE PREVIOUSLY DEFINED VARIABLES.  

Each of these elements must be included in a unique container within a dashboard.

Select the container and with the right click choose the “Add Show/Hide Button” option, which will allow, with a single click, to hide or show this container with all its elements.

CREATE THE MAIN CHART

STEP 8. CALCULATE THE VARIABLES FOR THE MAIN PLOT.

AxisDATE(CASE [P.Time Unit View]
WHEN 1 THEN [Order Date]
WHEN 2 THEN DATETRUNC(“week”, [Order Date])
WHEN 3 THEN DATETRUNC(“month”, [Order Date])
WHEN 4 THEN DATETRUNC(“quarter”, [Order Date])
WHEN 5 THEN DATETRUNC(“year”, [Order Date])
END)
Start DateDATE(CASE [P.Time Unit Filter]      

WHEN 1 THEN    // DAY        
CASE [P.Date Period Filter]            
WHEN 1 THEN [Anchor Date Adjust] – 1            
WHEN 3 THEN [Anchor Date Adjust] + 1            
WHEN 4 THEN [Anchor Date Adjust] – [P.N] + 1            
ELSE [Anchor Date Adjust]        
END     

WHEN 2 THEN    // WEEK        
CASE [P.Date Period Filter]            
WHEN 1 THEN DATEADD(“week”, -1, [Anchor Date Adjust])            
WHEN 3 THEN DATEADD(“week”, 1, [Anchor Date Adjust])            
WHEN 4 THEN DATEADD(“week”, -[P.N] + 1, [Anchor Date Adjust])            
ELSE [Anchor Date Adjust]        
END     

WHEN 3 THEN    // MONTH        
CASE [P.Date Period Filter]            
WHEN 1 THEN DATEADD(“month”, -1, [Anchor Date Adjust])            
WHEN 3 THEN DATEADD(“month”, 1, [Anchor Date Adjust])            
WHEN 4 THEN DATEADD(“month”, -[P.N] + 1, [Anchor Date Adjust])            
ELSE [Anchor Date Adjust]        
END     

WHEN 4 THEN    // QUARTER        
CASE [P.Date Period Filter]            
WHEN 1 THEN DATEADD(“quarter”, -1, [Anchor Date Adjust])            
WHEN 3 THEN DATEADD(“quarter”, 1, [Anchor Date Adjust])            
WHEN 4 THEN DATEADD(“quarter”, -[P.N] + 1, [Anchor Date Adjust])            
ELSE [Anchor Date Adjust]        
END     

WHEN 5 THEN    // YEAR        
CASE [P.Date Period Filter]            
WHEN 1 THEN DATEADD(“year”, -1, [Anchor Date Adjust])            
WHEN 3 THEN DATEADD(“year”, 1, [Anchor Date Adjust])            
WHEN 4 THEN DATEADD(“year”, -[P.N] + 1, [Anchor Date Adjust])            
ELSE [Anchor Date Adjust]        
END 
END)
End DateDATE(CASE [P.Time Unit Filter]  
    
WHEN 1 THEN     // DAY        
CASE [P.Date Period Filter]            
WHEN 4 THEN [Anchor Date Adjust]            
WHEN 5 THEN [Anchor Date Adjust] + [P.N] – 1            
ELSE [Start Date]        
END     

WHEN 2 THEN    // WEEK        
CASE [P.Date Period Filter]            
WHEN 4 THEN DATEADD(“week”, [P.N], [Start Date]) – 1            
WHEN 5 THEN DATEADD(“week”, [P.N], [Start Date]) – 1            
WHEN 6 THEN [Anchor Date]            
ELSE [Start Date] + 6        
END     

WHEN 3 THEN    // MONTH        
CASE [P.Date Period Filter]            
WHEN 4 THEN DATEADD(“month”, [P.N], [Start Date]) – 1            
WHEN 5 THEN DATEADD(“month”, [P.N], [Start Date]) – 1            
WHEN 6 THEN [Anchor Date]            
ELSE DATEADD(“month”, 1, [Start Date]) – 1        
END     

WHEN 4 THEN    // QUARTER        
CASE [P.Date Period Filter]            
WHEN 4 THEN DATEADD(“quarter”, [P.N], [Start Date]) – 1            
WHEN 5 THEN DATEADD(“quarter”, [P.N], [Start Date]) – 1            
WHEN 6 THEN [Anchor Date]            
ELSE DATEADD(“quarter”, 1, [Start Date]) – 1        
END     

WHEN 5 THEN    // YEAR        
CASE [P.Date Period Filter]            
WHEN 4 THEN DATEADD(“year”, [P.N], [Start Date]) – 1            
WHEN 5 THEN DATEADD(“year”, [P.N], [Start Date]) – 1            
WHEN 6 THEN [Anchor Date]            
ELSE DATEADD(“year”, 1, [Start Date]) – 1        
END 
END)
Filter[Order Date] >= [Start Date] AND [Order Date] <= [End Date]

STEP 9. DEFINE THE VIEW OF THE MAIN CHART.

Move the “Axis” field to the columns shelf and the “Sales” field to the rows shelf.

Move the “Filter” field to the filter shelf and select the value True.

Move the “Start Date”, “End Date” fields and the “P.Time Unit View” parameter to the detail shelf.

STEP 10. CREATE THE FOLLOWING PARAMETER ACTIONS.

PARAMETER ACTIONSOURCETARGET PARAMETERFIELD OR VALUEAGGREGATION
1.Time Unit View1.Time Unit View (TUV)P.Time Unit ViewTUV ScaffoldNone
2.Time Unit Filter2.Time Unit Filter (TUF)P.Time Unit FilterTUF ScaffoldNone
3.Anchor Type3.Anchor Type (AT)P.Anchor TypeAT ScaffoldNone
4.Date Period Filter4.Date Period Filter (DPF)P.Date Period FilterDPF ScaffoldNone
Update TUF1.Time Unit View (TUV)P.Time Unit FilterUpdate TUFNone
Update DPF2.Time Unit Filter (TUF)P.Date Period FilterUpdate DPFNone

Link here

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag).