-
1. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Shawn Wallwork Nov 25, 2013 9:54 AM (in response to Leigh Fonseca)1 of 1 people found this helpfulLeigh 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
-
2. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Leigh Fonseca Nov 25, 2013 7:22 PM (in response to Shawn Wallwork)Shawn,
You came through again! Thanks a bunch. I also really appreciate the tip about Special > Not-Null Dates. I wouldn't have know to check this and your explanation about the error help me better understanding the IF logic. Hope to buy you a drink and say thanks at the next TCC.
BTW, let me know if you're still looking for Arbitron Metros -- I have a tip.
Cheers,
Leigh -
3. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Shawn Wallwork Nov 26, 2013 1:27 AM (in response to Leigh Fonseca)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
-
4. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Rusty Evans Sep 9, 2015 11:00 AM (in response to Leigh Fonseca)The problem I'm coming across with this solution, is my dates are auto rolled up to the year?
-
5. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Leigh Fonseca Sep 11, 2015 7:58 AM (in response to Rusty Evans)Hi Rusty,
Could you post a packaged workbook with your date functionality? It's hard to diagnose what's going on without being able to see how you've set it up.
Kind regards,
Leigh
-
6. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Daniel Schauder May 18, 2017 2:38 PM (in response to Leigh Fonseca)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!
-
7. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Leigh Fonseca May 19, 2017 2:10 PM (in response to Daniel Schauder)Hi Daniel,
Thanks for offering another approach to this problem. Since I posted it, and LOD expressions have been released, I've found people pretty satisfied with date functionality that offers a pick list of date options and a Custom Dates option. This also allows users to quite easily get "Month to Date" simply by selecting the Current Month. I've used several variations of this with client's fiscal calendars as well. Hopefully this will add another option. The performance is quite good too.
[Date Filter]
IF [Select Time Frame] = 0 AND DATEPART('week',[Order Date]) = {FIXED : MAX(DATEPART('week',[Order Date]))} THEN 1
ELSEIF [Select Time Frame] = 1 AND DATEPART('month',[Order Date]) = {FIXED : MAX(DATEPART('month',[Order Date]))} THEN 1
ELSEIF [Select Time Frame] = 2 AND DATEPART('week',[Order Date]) = {FIXED : MAX(DATEPART('week',[Order Date]))} - 7 THEN 1
ELSEIF [Select Time Frame] = 3 AND DATEPART('month',[Order Date]) = {FIXED : MAX(DATEPART('month',[Order Date]))} - 1 THEN 1
ELSEIF [Select Time Frame] = 4 AND [Order Date] >= [Start Date] AND [Order Date]<= [End Date] THEN 1
END
-
date-options2.twbx 1.2 MB
-
-
8. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Nishita Tamuly Jun 12, 2017 12:41 PM (in response to Leigh Fonseca)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!!
-
9. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Nishita Tamuly Sep 18, 2017 3:50 PM (in response to Nishita Tamuly)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
-
10. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Dan Dan Mar 2, 2018 9:01 AM (in response to Shawn Wallwork)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
-
11. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Lori Kandyba Mar 2, 2018 10:38 AM (in response to Dan Dan)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
-
12. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Dan Dan Mar 2, 2018 1:14 PM (in response to Lori Kandyba)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
-
13. Re: Custom Dates AND Relative Dates as a Selection on a Single View
Naga sivakrishna Bezawada Jul 16, 2019 5:31 AM (in response to Leigh Fonseca)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?