5 Replies Latest reply on May 18, 2016 10:48 AM by Shinichiro Murakami

    Moving Date Range Filter

    Chrissy Scott

      Hi All,

       

      I need some help setting up a date range filter. I have a dashboard that's specific to data from Friday at 2:00 pm - Monday at 10:00 am. I need the dashboard data to reset every Monday after 10:00 am. So for example, today is Wednesday 5/11 so the dashboard currently shows data from Friday 5/13 2:00 pm - Monday 5/16 10:00 am. On Monday 5/16 after 10:00 am, I need the dashboard to display the data for Friday 5/20 2:00 pm - Monday 5/23 10:00 am. And so on... each week.

       

      Is there a way to do this using data source filters? Or will this require a calculated field of some kind?

       

      Thoughts?

       

      Thanks,

      Chrissy

        • 1. Re: Moving Date Range Filter
          Shinichiro Murakami

          You can use "today()" instead of [Dummy Today] in actual data.

           

          Convert 2:00 pm to 12:00 am

          [Date time 2pm ==> 12 am]

          dateadd('hour',-14,[Date Time])

           

          Date time 10am ==> 12 am

          dateadd('hour',-10,[Date Time])

           

          Get Last Friday from today ( int this case "Dummy Today")

          [Date last Friday]

          datetrunc('week',[Dummy Today],"Friday")

           

          [Date last Monday]

          DATEtrunc('week',[Dummy Today],"Monday")

           

          [Filter]

          [Date time 2pm ==> 12 am]>=[Date last Friday]

          and [Date time 10am ==> 12 am]<= [Date last Monday]

           

          If you are using Tableau Server with extract data, you can set Extract Refresh schedule on Monday 10:00 am every week.

          If not, before next Monday. You still can use today() as filtering criteria.

           

          Thanks,

          Shin.

           

          9.2 attached

          • 2. Re: Moving Date Range Filter
            Chrissy Scott

            Hi Shinichiro,

             

            This has been really helpful and I'm a lot closer to a solution so thank you.

             

            I have some more questions for you:

            • How do I create a field to give me the date NEXT Friday?
            • How do I create a field to give me the date NEXT Monday?

             

            Once the week starts over, I need to look at the coming weekend instead of the past one.

             

            Thanks again,

            Chrissy

            • 3. Re: Moving Date Range Filter
              Shinichiro Murakami

              It's easy enough to reach with useing one common formula.

               

              datetrunc('week',today(),'Friday')+7

              datetrunc('week',today(),'Monday')+7

               

              or

               

              datetrunc('week',today()+7,'Friday')

              datetrunc('week',today()+7,'Monday')

               

              BTW,

              Could you mark the answer as "Cirrect" ?

               

              Thanks,

              Shin

               

               

              Date Functions

              DATETRUNC(date_part, date, [start_of_week])

              Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The start_of_week parameter is optional. If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

              Examples

              DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM

              DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM

              • 4. Re: Moving Date Range Filter
                Chrissy Scott

                Hi Shinichiro,

                 

                I can't quite get this to work. The Next Friday and Next Monday calculations are working well. I believe the discrepancy is with the specific times. Can you explain why the times need to be converted to 12am?

                • 5. Re: Moving Date Range Filter
                  Shinichiro Murakami

                  To get specific "time".

                  I mean you need to exclude 1:59 PM, but need to include 2:00 pm on Friday.

                  With shifting the hours by -14 hours, 2:00 pm becomes 12:00 am on same date of Friday, then easy o handle only by date granuarity.

                  I need the dashboard to display the data for Friday 5/20 2:00 pm - Monday 5/23 10:00 am. And so on... each week.

                   

                  Thanks,

                  Shin