5 Replies Latest reply on Jun 14, 2018 1:07 PM by Brian Vu

# Splitting 53 weeks across two years

Hi all,

I'm attempting to make a weekly value tracker and can't seem to get over this hurdle of splitting the 53rd week across two years:

Ideally I would want to see:

YearWeek
WeekStartWeekEnd
20155312/28/201512/31/2015
201611/1/20161/3/2016
201621/4/20161/10/2016

So that the first week of every year may have 1 - 7 days. This particular set-up starts weeks on Mondays.

Similarly for 2016-2017...

YearWeek
WeekStartWeekEnd
20165312/26/201612/31/2016
201711/1/20161/1/2016
201721/2/20171/8/2017

From here I would be able to sum the # of records and divide over the number of days to get a Records/Day average. Users would be able to select which years and weeks they're interested in.

I've looked at this post which seems to describe exactly what I need - and sounds likes its basic Tableau functionality - but I have not been able to get it to work: Week in, week out, Tableau - Clearly and Simply

Unfortunately I can't post a workbook because it contains proprietary company data.

• ###### 1. Re: Splitting 53 weeks across two years

Hi Brian,

I guess you are using Week of Week start date and that's the reason you are facing this issue.

You can actually calculate the week number based on dayofyear / 7 calculation.

Hope this helps.

• ###### 2. Re: Splitting 53 weeks across two years

My mistake, I missed a simple component of that calculation. This is necessary for the edge cases.

• ###### 3. Re: Splitting 53 weeks across two years

Hi Jeevan,

This isn't quite what I was looking for - I just tested and what your Calculated Field does is that it makes Week 1: Jan 1 - Jan 7 (i.e. a full seven days) for all years, Week 2: Jan 8 - Jan 14 and so on. If it were up to me this would be the way I would lay it out; however, my users want it such that the first week has a variable number of days.

To explain a bit about what Week of Week Monday is... I named a Calculated Field Week Monday so that I could shift the start date of the week from Tableau's default Sunday to Monday - DATETRUNC('week',[DTTMSTART],'Monday'). I wrap this with a WEEK() function on the Rows so I can see the actual # of the week (e.g. Week 12).

WeekStart - MIN([Week Monday]), gets the first day of each week

WeekEnd - DATEADD('day',-1, DATEADD('week',1,MIN([Week Monday]))), gets the last day of each week

• ###### 4. Re: Splitting 53 weeks across two years

HI Brian,

Not 100% sure, but this probably helps.

Thanks,

Shin

• ###### 5. Re: Splitting 53 weeks across two years

This worked like a charm. I appreciate it, thank you.