13 Replies Latest reply on Jul 16, 2019 5:31 AM by Naga sivakrishna Bezawada

    Custom Dates AND Relative Dates as a Selection on a Single View

    Leigh Fonseca

      I have several clients who would like the ability to have a report default to a relative date and also be able to apply a custom date.  For example, each time I view the report it would automatically load data for the last 7 days.  But I also want the ability to select custom dates to override this selection. 

       

      I can offer either relative date filtering using Tableau's native Date Relative filter or I can offer custom dates using Parameters but I'm having a lot of difficulty bringing these together.  Here's what the parameter that offers these four options might look like, and it would be accompanied by two additional parameters for the Custom Dates Start Date and End Date.

       

      date-options.png

       

      When I try to decode this parameter I get errors for both an IF and a CASE statement because the code contains a boolean. 

       

      I am often asked for this functionality and I'd very much like to deliver on it. I look forward to your suggestions and thank you in advance for your help.

       

      Cheers,

      Leigh

       

      Packaged workbook posted to Public.

        • 1. Re: Custom Dates AND Relative Dates as a Selection on a Single View
          Shawn Wallwork

          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

          1 of 1 people found this helpful
          • 2. Re: Custom Dates AND Relative Dates as a Selection on a Single View
            Leigh Fonseca

            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

              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

                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

                  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

                    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

                      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

                      • 8. Re: Custom Dates AND Relative Dates as a Selection on a Single View
                        Nishita Tamuly

                        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

                          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

                            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

                              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

                                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; showing yesterday issue.PNG

                                 

                                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

                                  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?