7 Replies Latest reply on Jan 18, 2016 1:33 PM by Ashley Rayner

# Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

First time poster so please be easy if i am in the wrong place

I am relatively new to tableau and so is our company, we have been on all the training we can but I have come to a point where the knowledge we have isn't enough.

We use a custom reporting calendar based on  4-4-5 periods. Always run Monday to Sunday

I am trying to build a daily kpi report which is automated one of the requirements is

Yesterday vs relative day last year,

Week to Date vs relative week to date last year,

Period to Date vs period to date last year

and finally Year to date vs year to date last year.

I have found posts which help me with year to date, last year vs this year, same for month to date

Where i am struggling is the auto filter for my custom periods.

Apologies if this is the incorrect forum

Hope some pointers can be given

thanks

Ashley

• ###### 1. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

First, I'd like to welcome to to the Tableau Forums! You are definitely posting in the right place.

I am a bit unclear what you mean by "4-4-5 periods." If you can attach a packaged workbook (.twbx), showing what you have successfully accomplished, and the problems you are having, we can be of better assistance. Please see Tableau Forum Guidelines, Posting a Perfect Question |Tableau Support Community and Anonymize your Tableau Package Data for Sharing |Tableau Support Community for more information.

1 of 1 people found this helpful
• ###### 2. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

Thanks for the welcome Bill.  I will anonymize the data now and upload, thanks for the links.

• ###### 3. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

As suggested attached is the packaged workbook

4-4-5 is a representation of the number of weeks in a quarter.

The structure goes as follows, Year, Quarter, Period, Week

I have tried to show you how a typical year works. The start date therefore is not always the same but will be a Monday

QuarterPeriod
Weeks
114
24
35
244
54
65
374
84
95
4104
114
125 (Sometimes 6)

Hope this makes sense.

• ###### 4. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

Thank you. This is a great base on which to begin. Now, what exactly are you looking to produce? Can you be specific on the formula? Have you attempted it? If so, where did it go wrong?

• ###### 5. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

I am looking to do the following, using 16/1 as an example date

Assuming the fiscal year starts on 29/6/15 and using the 4-4-5 method

17/01/16 is a Saturday,

1st of all I would like to compare 16/01/16 Saturday vs 17/1/2015 closest comparable Saturday from the previous year, this I have managed

2nd I would like to compare Period to date. 16/01/2016 falls into period 7 and it is the 20th day of this period.

I would like to compare 20 days of period 7 this year, vs 20 days of period 7 from last year, for the same relative days.

The same then needs to be done for Year to date,

I guess this could happen for all the calendar dimensions, week, period, quarter, half year and full year.

The reason for this is because the business is a very seasonal one with huge amounts of traffic at weekends so comparing January vs January in not a true reflection of business. One year may have 4 Saturdays and another 3 or 5.

I have tried a couple of approaches, I have tried creating Bins, calculated fields but I always back track.

Thanks for your time with this.  I am currently recruiting for a tableau expert so fingers crossed we as a business can learn quickly.

• ###### 6. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

Hi,

If you are having data Date level ( MM-DD-YYYY), then it is easier to solve the situation using Date calculations.

we have to create several calculated fields to solve all of your queries.

1) Yesterday vs  relative last year

Yesterday Sales

sum(if datediff('day', [Order Date], today())= 1 then [Sales] end)

last year yesterdauy Sales

sum(if datediff('day', [Order Date], dateadd('year', -1, today() )  )= 1 then [Sales] end)

diffe of Yeaterday & Same day last year

[Yesterday Sales] - [last year yesterdauy Sales]

similarly we can create calculations, if you want to know list of Date functions available in tableau, go through the following link.

YTD Calculation

The following link gives you clear idea on how to use the Date functions in Calculated fields.

Tableau Viz: Date Functions - Tableau

Best Regards

Kumar

• ###### 7. Re: Calculating Year to date, Period to date, Week to date all verse last year using a custom calendar

Thanks Kumar

how does this work with a custom year start date?

my years run

2015 = 30/6/2014 -> 28/6/2015

2016 = 29/6/2015 -> 26/6/2016