8 Replies Latest reply on Apr 22, 2019 7:22 AM by Simon Gibb

# Time periods in Tableau

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

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

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

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

• ###### 4. Re: Time periods in Tableau

I was looking for this...

Thanks

• ###### 5. Re: Time periods in Tableau

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

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

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
• ###### 8. Re: Time periods in Tableau

Hi Mark
This may be a long shot but if you still use this sheet, did you ever update it with the quarters and Years and / or can you please explain how you can add This Year and Last Year to Date but using a Financial Year start?
Thanks

Simon