4 Replies Latest reply on Dec 11, 2012 7:16 AM by Craig Willis

    yesterday date filter

    andrey uvarov

      Hello

       

      I use a filter by date in my report. Opening a report, i want to automatically set the dates yesterday, how can I do this?

      I also want to save the ability to filter other dates.

        • 1. Re: yesterday date filter
          Tracy Rodgers

          Hi Andrey,

           

          One way to have Yesterday always selected would be to create a calculated field similar to the following:

           

          if [Order Date]=dateadd('day', -1, today()) then 'Yesterday'

          else 'Other Dates'

          end

           

          Then, you can show this quick filter, as the quick filter for Order Date (setting it to Show Only Relevant Values).

           

          Hope this helps a bit!

           

          -Tracy

          • 2. Re: yesterday date filter
            Darin Coulter

            I use the relative date filter option a lot of the time when I'm building dashboards for end users who don't care or need to see much more than that relative date.  It can be set to be anchored to a specific date (or Today by default).  I find it useful and it sounds like you may be able to use it too.  When you drop your date field on the Filters shelf, select the first option, 'Relative date' and hopefully you find something useful if you don't prefer Tracy's calculated field option.

             

            Lots of folks here are only concerned with the current month or yesterday or last month.  If you have scenarios where you look at something 7 days a week or you don't have Sunday data when you come in on Monday and you don't need to look at Friday or Saturday... then using that Relative Date filter option is always a nice, quick trick.  Another reason I love Tableau, it seems to have a way for everyone to find what they like and how it applies to their day

            • 3. Re: yesterday date filter
              Tom Bash

              I have a similar problem but am not sure this solves it. When a user first opens the dashboard I want it to only show the past two weeks, but I also want users to have the option to say, only look at a 10 day period from three months ago by editing the range of dates filter. In this scenario I'm not always showing yesterday, and if a user tried to look at the past three months of data all at once it wouldn't fit on the dashboard and everything would turn into #####.

              • 4. Re: yesterday date filter
                Craig Willis

                Use a parameter where the user can select a date and a calculated field to see the trailing 2 weeks.  The formula I use is below

                 

                (Quick aside:  Tracy is there a reason you use dateadd instead of datediff, do they accomplish the same thing or is there a deeper and more efficient reason for your choice?)

                 

                IF DATEDIFF('day',[parameterdate],[datasource_date]<=10 THEN 'TRUE'

                     ELSE 'FALSE'

                END

                 

                Then put that calculated field in your filters shelf.  I've only ever used this when changing the amount of time you can look back from today.  For instance the parameter selection allows the user to look back 1 3 6 12 or 24 months.  I think the same principle should work here but depending on your overall date range you could have a lot of potential selections which might be confusing.  I've never used it before but there is an add from field option when creating parameters, is it possible this could allow the user to select any date for which you have data?