2 Replies Latest reply on Mar 9, 2017 6:45 AM by Filipe Hemsworth

# Comparing Week to date values Year on Year

Hi everyone,

A little while ago I created a report which would summarize sales for all of our products. One of the features of this report is the ability to compare sales for the period to date, to the same period to date last year.

I got the Date Comparison functions from another Tableau Vizer on the forums:

Using his method I was successfully able to create Year to Date, Quarter to Date, and Month to date values.

First I created a calculated field to calculate this year's period to date Sales to date using the following code:

IF

(DATETRUNC([Period], [Date]) =

DATETRUNC([Period], [Date Selection])

AND

DATETRUNC('day', [Date]) <=

DATETRUNC('day', [Date Selection]))

THEN

[Sales]

END

Then this for Last year period to date:

IF

DATETRUNC([Period], [Date]) =

AND

DATETRUNC('day', [Date]) <=

THEN

[Sales]

END

The [Date Selection] field is a simple date parameter I created, and the [Period] is the parameter used to define the period to compare to, which contains the values 'day', 'week, 'month', 'quarter', and 'year'

My question to you all is; how would I go about getting my week period working? Taking the example of 04/12/2016 which the last day of the week (starting Monday 28/11/2016). I want to compare this to the same Monday - Sunday last year. The formula I have used is indeed picking up the start of the week last year correctly (30/11/2016), but as this year's week ends on the 04/12/2016, it is also cutting off last year's week on the 04/12/2015 (instead of the 06/12/2015)

Any ideas?

• ###### 1. Re: Comparing Week to date values Year on Year

I think these two pieces of information could be helpful to you:
1. DATETRUNC has an optional [start_of_week] parameter. You can set it to whatever you want so that your function has the following format: DATETRUNC(date_part, date, [start_of_week]).

2. If you did not specify the start of week, Tableau will use the data source to determine the start of week. You can learn more here: Date Properties

I've created a blog post about time period comparison using a similar approach. You can find my solution, together with the workbook, there.

Hope this helps!

• ###### 2. Re: Comparing Week to date values Year on Year

In-case others have had this issue, here is the solution I ended up using. A bit of a rough work-around, but it should work for the next few years until the number of days between dates year on year change from 2.

IF

DATETRUNC([Period], [Date]) =

AND

DATETRUNC('day', [Date]) <=

IIF(STR([Period])="week",