Tableau - Filter based on parameter

Question

I have a dataset of dates. It has just one column CreatedOnDate and its values are in datetime as shown below.

This dataset has 6 months of values as shown. I have a parameter called Report Type which has possible values Monthly, Weekly, Daily (Screenshot below)

I have created a calculated field (called Created On Date) which converts the date based on Report Type selected. The formula is shown below

CASE [Report Type]
    WHEN "Monthly" THEN DATENAME('month', [CreatedOnDate])
    WHEN "Weekly" THEN "Week " + STR(DATEPART('week',[CreatedOnDate]))
    WHEN "Daily" THEN STR(MONTH([CreatedOnDate])) + "/" + STR(DAY([CreatedOnDate])) + "/" + STR(YEAR([CreatedOnDate]))
END

This works perfectly. The result of the calculated field is shown below.

I now need to incorporate the following logic

IFF Report Type = "Daily" Display only the last 30 days in the dataset
Other cases Show all values

How do I achieve this?

Petr Havlik Petr Havlik · Accepted Answer · 2016-10-07T08:25:32

woodhead92, I'd suggest using so called Level of Detail expressions that were introduced in Tableau v8. First create a calculated field that will calculate the most recent (=MAX) date available:

{FIXED : MAX(CreatedOnDate) }

Let's call this MaxDate LOD. Then adding a new calculated field Show/Hide:

IF [Report Type] = "Daily" AND 
  ([CreatedOnDate] >= DATEADD('day', -30, [MaxDate LOD]) THEN 'Show'
ELSEIF [Report Type] = "Weekly" OR [Report Type] = "Monthly" THEN 'Show'
ELSE 'Hide'
END

Add this filter and select 'Show' value only. I am assuming that you want to see all dates when Weekly/Monthly date granularity is selected - if that's not the case, simply add more ELSEIF conditions.

The formula above could be simplified, but I wanted to make it as verbose as possible so that it helps you understand how Level of Detail expressions work.

One thing to keep in mind - FIXED LOD calculation overwrites filters, so if you have a date-range filter available, you will have to make sure it's added to context. More details on filter context are available here in this a bit out-dated, but still excellent blog post.

Tableau - Filter based on parameter

2 Answers