Tableau - Creating Overlapping Date Bins

Question

I have a report that will be updated Monday through Friday and want to display a single metric [Productivity %] across several different date [In_Date] "bins" i.e. a generated date dimension that would bin my data according to the following definitions:

1. Yesterday
2. Week to date (e.g. on Thursday, Sunday through Wednesday data would be displayed etc.)
3. Last 7 days
4. Month to date
5. Last full month
6. Last 3 months
7. Last 6 months
8. Last 12 months

I'm not looking to create a parameter that the user would use to toggle; rather, I want a new dimension that would be dropped in the columns section that would follow the bins outlined above.

Below is an illustration of what I want to achieve:

Currently, I have the below result using this code:

IF [In Date] = DATEADD('day', -1, TODAY()) THEN 'Yesterday'

ELSEIF [In Date] < TODAY() AND [In Date] >= DATEADD('day', -ISOWEEKDAY(TODAY()), TODAY()) then 'Week to Date'

ELSEIF [In Date] < TODAY() AND [In Date] >= DATEADD('day', -7, TODAY()) THEN 'Last 7 Days'

ELSEIF [In Date] < TODAY() AND [In Date] >= DATEADD('day', -day(TODAY())+1, TODAY()) then 'Month to Date'

ELSEIF [In Date] < DATETRUNC('month', TODAY()) AND [In Date] >= DATEADD('month', -1, DATETRUNC('month', TODAY())) then 'Last Month'

ELSEIF [In Date] < DATETRUNC('month', TODAY()) AND [In Date] >= DATEADD('month', -3, DATETRUNC('month', TODAY())) then 'Last 3 Months'

ELSEIF [In Date] < DATETRUNC('month', TODAY()) AND [In Date] >= DATEADD('month', -6, DATETRUNC('month', TODAY())) then 'Last 6 Months'

ELSEIF [In Date] < DATETRUNC('month', TODAY()) AND [In Date] >= DATEADD('month', -12, DATETRUNC('month', TODAY())) then 'Last 12 Months' ELSE 'Older' END

The overlapping date bins are not capturing all of the data: "last 12 months" omits metrics previously captured in the "last 6 months," etc. Moreover, the first 4 bins (yesterday, WTD, last 7 days, and MTD) are missing entirely.

Answer 1

This is a proper case of use of PARAMETERS in Tableau. I enjoyed while solving this.

Since you have not provided any data, I created a dummy data in excel by generating dates from 1-7-2020 onwards till date (DATE_GIVEN) and filling some random numbers against these as MEASURE_1. Something like this.

Now proceed like this.

Step-1 Create a parameters with 8 inputs (as listed in your question). I created for first 6 and leaving remaining for you. The parameter should be like

Step-2 Create a calculated field date bins with the following calculation

CASE [Parameter 1]
WHEN 1 THEN (
IF [Date_Given] = DATEADD('day', -1, TODAY()) THEN [Date_Given] end)
WHEN 2 THEN 
( IF [Date_Given] < TODAY() AND [Date_Given] >= DATEADD('day', -ISOWEEKDAY(TODAY()), TODAY()) then [Date_Given] END )
WHEN 3 then
( IF [Date_Given] < TODAY() AND [Date_Given] >= DATEADD('day', -7, TODAY()) THEN [Date_Given] END )
WHEN 4 THEN
( IF [Date_Given] < TODAY() AND [Date_Given] >= DATEADD('day', -day(TODAY())+1, TODAY()) then [Date_Given] END )
WHEN 5 THEN
( IF [Date_Given] < DATETRUNC('month', TODAY()) AND [Date_Given] >= DATEADD('month', -1, DATETRUNC('month', TODAY())) then [Date_Given] END)
WHEN 6 THEN
( IF [Date_Given] < DATETRUNC('month', TODAY()) AND [Date_Given] >= DATEADD('month', -3, DATETRUNC('month', TODAY())) then [Date_Given] END)
END

Needless to say you have to incorporate appropriate calculation for remaining two values

Step-3 Place date bins on rows shelf and additionally on FILTERS CARD. (Filter out only null values from this .. special values tab). Add measure_1 to desired type. Show parameter 1 and your view is ready. Check the screenshots (Today's system date - 16-12-2020)

OR

OR

OR

Answer 2

As regards your edited question, I propose a workaround (because creation of a calculated field where one value input can result in multiple value outputs seems illogical and impossible to me given the basic rules of mathematics) as follows.

Create 8 different calculated fields as

Last 3 months M

IF [Date_Given] < DATETRUNC('month', TODAY()) AND [Date_Given] >= DATEADD('month', -3, DATETRUNC('month', TODAY())) then [Measure_1] END

similarly for other desired bins create a separate calculated field. You can thereafter build a view/viz as included in your question.