Leigh Welcome back. When you get that boolean error it really only applies to anything after the "THEN" part of the statement. By definition an IF statement IS boolean, so you just need to move the logic in front of the THEN, like this:

IF [Date Selection]=1 AND Date <= today() and Date >= dateadd('day', -6, today()) THEN [Date]

ELSEIF [Date Selection]=2 AND Date <= today() and Date >= dateadd('week', -4, today()) THEN [Date]

ELSEIF [Date Selection]=3 AND Date <= today() and Date >= dateadd('month', -2, today()) THEN [Date]

ELSEIF [Date Selection]=4 AND [Date] <= [End Date] and [Date] >= [Start Date] THEN [Date]

END

The 'trick' is that when you drop this calc on the filter shelf you need to first click 'Next' then 'Special Values' then 'Non-null dates' otherwise the filter won't work. (See attached.)

Cheers,

--Shawn

Leigh, glad to help. I think this would make a good TabWiki. I like the way you were able to combine predefined date ranges with custom start/end dates. I think it would be useful to others. Please consider writing up a post there.

Yes I'd love a way to map Arbitron Metros! Tip me. My email is on my profile if it's a secret tip.

Cheers,

--Shawn

The problem I'm coming across with this solution, is my dates are auto rolled up to the year?

Hi Leigh,

This is an issue that has plagued me for a long while, so thanks for posting your solution. I like Tableau's built-in relative date filter, and I wasn't crazy about having to hard-code the relative date options, so I took your approach a step further.

The strategy is as follows:

1.Duplicate your data source

2.Create a field with a constant value in both versions of the data source

3.Edit your relationships to blend on the constant field

4.Apply a relative date filter on the duplicate data source

5.Create calculated fields to get the min and max dates from the duplicate date source

6.Create 2 date parameters and a parameter to switch between the relative date and a custom date range

7.Create a calculated field in the original data source - when the user selects the relative date range, compare the date value in the original data source with the min and max date ranges from the duplicate data source. When the user selects the custom date range, compare the date value in the original data source with the 2 custom date parameters

The attached workbook shows this strategy in action.

The potential downside of this is that it forces you to use blending (impacts performance), and creates a duplicate data source (pretty inefficient).

I am hopeful that Tableau will release a better date filter option that gives the user this flexibility without having to do painful workarounds like this. In the meantime, I hope this solution helps!

Hi Leigh,

Thank you for updating this solution.

I applied this tour dataset and it works perfectly when there is only one year worth data to analyze.

Do you know a workaround the above formula to take into account multiple years? With the current formula, the custom date selection works well but the max date LODs don't work since it takes the max month from all of the dataset for multiple years. So current month even though it is June, shows data from December as we have the previous year's data in there too.

I would really appreciate any feedback on this if you have come across this situation.

Thank you!!

Hi All,

If your database has historical data for more than year you can modify this calc to take into account month and year to display accurate info:

IF [Select Time Frame] = 0 AND DATEPART('week',[Report Date]) = {FIXED : MAX(DATEPART('week',TODAY()))}

AND DATEPART('month',[Report Date]) = {FIXED : MAX(DATEPART('month',TODAY()))}

AND DATEPART('year',[Report Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

THEN 1

ELSEIF  [Select Time Frame] = 1 AND DATEPART('month',[Report Date]) = {FIXED : MAX(DATEPART('month',TODAY()))}

AND DATEPART('year',[Report Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

THEN 1

ELSEIF [Select Time Frame] = 2 AND  DATEPART('week',[Report Date]) = {FIXED : MAX(DATEPART('week',TODAY()))} - 1

AND DATEPART('month',[Report Date]) = {FIXED : MAX(DATEPART('month',TODAY()))}

AND DATEPART('year',[Report Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

THEN 1

ELSEIF  [Select Time Frame] = 3 AND DATEPART('month',[Report Date]) = {FIXED : MAX(DATEPART('month',TODAY()))} - 1

AND DATEPART('year',[Report Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

THEN 1

ELSEIF [Select Time Frame] = 4 AND [Report Date] >= [Start Date] AND [Report Date]<= [End Date] THEN 1

END

Hi, can i ask how i would show today, yesterday, last 7 days and last 30 days along with a custom from and to time filter please?

Thanks,

Daniel

Hi Daniel,

Building on Leigh and Shawn's work, your filter variable should look something like this:

[Date Filter]

IF [Select Time Frame] = 0 AND [date] = today() THEN 1 // today

ELSEIF [Select Time Frame] = 1 AND [date] <= today() and [date] >= dateadd('day', -1, today()) THEN 1 // yesterday

ELSEIF [Select Time Frame] = 2 AND [date] <= today() and [date] >= dateadd('day', -6, today()) THEN 1 // last 7 days

ELSEIF [Select Time Frame] = 3 AND [date] <= today() and [date] >= dateadd('day', -29, today()) THEN 1 // last 30 days

ELSEIF [Select Time Frame] = 4 AND [date] >= [Start Date] AND [date]<= [End Date] THEN 1 // custom dates

END

and your selection parameter like this:

Along with your start and end date parameters. Hope this helps!

Lori

Hi Lori, thanks for coming back to me on this one.

I tried the formula but the view when selecting yesterday shows both of yesterday and today, as below;

Taking the posts earlier, I managed to get it working using the formula below which was taken from Shawn's posts earlier and i took out one of the equal to's for the yesterday selection and it seems to work!!

IF [Date Selection]=1 AND Date <= today() and Date >= dateadd('day', -0, today()) THEN [Date]

ELSEIF [Date Selection]=2 AND Date < today() and Date >= dateadd('day', -1,today()) THEN [Date]

ELSEIF [Date Selection]=3 AND Date <= today() and Date >= dateadd('day', -6, today()) THEN [Date]

ELSEIF [Date Selection]=4 AND Date <= today() and Date >= dateadd('day', -29, today()) THEN [Date]

ELSEIF [Date Selection]=5 AND [Date] <= [End Date] and [Date] >= [Start Date] THEN [Date]

END

Thanks for the help,

Daniel

Hi Leigh,

Nice post and explanation for understanding easily, but i need one more action to implement in that please help me out .

If we select except custom date button then is there any easy way to hide "startdate" and "enddate" parameters?