7 Replies Latest reply on Oct 29, 2015 3:48 PM by Wes Reneau

    Time periods in Tableau

    Mark Fraser

      A recent query on the forum prompted me to put together the attached (v8.3) where I use a parameter date to drive different time periods on a single sheet.

       

      Time calculations include:

      • Today
      • Yesterday
      • Week to Date (WTD) (defaults to Monday)
      • Month to Date (MTD)
      • Quarter to Date (QTD) (Jan-Mar is Q1)
      • Year to Date (YTD)
      • Year to Date Previous Year

       

      It may not be the most efficient, or best practice, but its how I do it, and I thought I would share

      If you have alternative methods, improvements, suggestions etc, feel free to comment!

       

      PS. Now added previous 7 days from parameter date: if 01/06/2015 is selected, week previous is 25th-31st May

      IF [Date] <= [Reference Date] AND DATEDIFF('day',[Date],[Reference Date])<= 7 AND DATEDIFF('day',[Date],[Reference Date])> 0 THEN [Random Value] END

       

      The previous week can easily be amended for previous week 2 weeks ago, by swapping to <=14 and >7, 3 weeks ago <=21 and >14 and so on. You could also group weeks together, previous 2 weeks combined is <=14 and >0.

       

      Previous month

      IF [Date] <= [Reference Date] AND DATEDIFF('month',[Date],[Reference Date])= 1 THEN [Random Value] END

       

       

       

      Previous 3 Months (excluding current month) - (amend the 3 as required... 6 month example below)

      IF [Date] <= [Reference Date] AND DATEDIFF('month',[Date],[Reference Date])<= 3 and DATEDIFF('month',[Date],[Reference Date])>0 THEN [Random Value] END

       

      Previous 6 months...(excluding current month)

      IF [Date] <= [Reference Date] AND DATEDIFF('month',[Date],[Reference Date])<= 6 and DATEDIFF('month',[Date],[Reference Date])>0 THEN [Random Value] END

       

      To include current month add = to the 0 side, so... DATEDIFF('month',[Date],[Reference Date])>=0

       

      Cheers

      Mark

        • 1. Re: Time periods in Tableau
          Ken Patton

          Thank you for putting this together!

           

          If we can figure out "Prior Quarter" and "Prior Year" this will be epic!  (ignoring Fiscal calendars, of course.    )

           

          Well done, sir.

          • 2. Re: Time periods in Tableau
            Joey Minix

            I create indexes for dateparts to handle prior quarter, prior year, etc..

             

            DATEDIFF('quarter', [Date], {fixed:max([Date]) } )

                 Then, if I want to do it as Mark has, I can just say

            IF [QuarterIndex] = 1 THEN [MyMeasure] ELSE 0 END

             

            Naturally, if your [Date] field doesn't include the current period, you'd want [QuarterIndex] = 0 for prior quarter

             

            If still in 8.3, for most applications you can replace {fixed:max([Date])}) with today(), but I prefer not to do that when I can avoid it.

             

             

             

            QuarterIndex = 0   # This Quarter

            QuarterIndex = 1   # Last Quarter

            ...

            QuarterIndex = 5 # Last Year-LastQuarter

            ...

            etc

             

            In many of my reports, I actually like to add a [ReportDate] to my dataset, and append data at the source rather than replace it.  I put a calendar in the report so users can select dates on the calendar to see how the data looked on that day (or month or week, etc).

             

            To keep dataset sizes manageable I keep only data where ReportDate > some value, 15 days ago, 30 days ago, 60 days ago, etc.

             

            In these cases, Date Indexes are really useful.

            2 of 2 people found this helpful
            • 3. Re: Time periods in Tableau
              danielle.weaver

              Well done! I find myself needing these standard date calculations in most of the dashboards I develop too.

              • 4. Re: Time periods in Tableau
                Jaideep Rokade

                I was looking for this...

                 

                Thanks

                • 5. Re: Time periods in Tableau
                  ashish.sathish

                  What if I am comparing week to week aggregation on the same date field?

                  I am fairly new to Tableau, Please bear with my naivety.

                  • 6. Re: Time periods in Tableau
                    Mark Fraser

                    Hi Ashish

                     

                    Welcome to the forums!

                    You can use Tableau for week on week comparison, how easy it is will depend on your data...

                     

                    Here is a basic example using the Superstore data

                    I use the inbuilt Difference function (shown below) to show week on week sales performance.

                    That is super easy - 2 minute job.

                    But as mentioned it depends on your data... if you have a sample you can provide with expected results I'll try take a look.

                    PS. If you post the question on the main forum it will have more visibility, otherwise it will just be me

                     

                    Cheers

                    Mark

                    • 7. Re: Time periods in Tableau
                      Wes Reneau

                      Hey Mark, Fantastic post!

                       

                      One question, how would you change the WTD calculation to start on the 1st of the month so you don't run into WTD > MTD situations when a month doesn't start on a Monday?


                      For example, this month (October 2015) started on a Thursday, meaning the WTD values would be larger than the MTD values since WTD would include September 28-30 while MTD would only include October 1-2.

                       

                      Thoughts?

                       

                      -Wes

                      2 of 2 people found this helpful