CODEWITHSUNDEEP
datefilterparameterstableau-api
woodhead92
woodhead92

Reputation: 127

Tableau - Filter based on parameter

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

enter image description here

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)

enter image description here

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.

enter image description here

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?

Upvotes: 1

Views: 971

Answers (2)

Petr Havlik
Petr Havlik

Reputation: 3317

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.

Upvotes: 1

Alex Blakemore
Alex Blakemore

Reputation: 11896

Create a calculated field for your condition and then place it on the filter shelf to include only rows that evaluate to true.

[Report Date] <> "Daily" or 
datediff('day', [CreatedOnDate], { max[CreatedOnDate] } < 30

The Curley braces are significant, an LOD calculation

Upvotes: 0

Related Questions