10 Replies Latest reply on Nov 6, 2012 2:06 PM by Alex Kerin

# Week Starting on a Monday

I am trying to compare Yoy by week number and I need to have the week beginning on Monday.  I cannot get both years to display the data in the same way. I was able to get the weeks of both years to begin on a Monday, but 2011 is showing the data on week two instead of week one. Is there some trick to get them in unison.

Week Calculation

• ###### 1. Re: Week Starting on a Monday

Does this work? Use the week aggregation of:

• ###### 2. Re: Week Starting on a Monday

It's still offset by a week.  Currently week one of 2011 is showing purchases for 1/2 then week 2 is showing purchases for 1/3-1/9. Week number 2 of 2011 should actually be week 1.

• ###### 3. Re: Week Starting on a Monday

So week 1 is the first Monday of any year? What are the days before the Monday?

• ###### 4. Re: Week Starting on a Monday

Days before the first Monday would be added to the previous year.

• ###### 5. Re: Week Starting on a Monday

Okay, this seems horribly complicated, but I got stuck trying it any other way.

From the way weeks work, it seemed we need to make the 1st Monday of any year equal to the 1st of January in a year that begins on a Sunday (which just happens to be 2012).

I came up with a calculation that shifts the date of the 1st Monday back to the 1st of the year, then moves the year to 2012.

We also then need a year calc to move the year portion.

There has to be a simpler way, but I cannot work it out - probably ditching the week aggregation provided by tableau and using some sort of MOD 7

• ###### 6. Re: Week Starting on a Monday

So yes, you can do a 'manual' week - it saves the move to 2012, but still has a variable shift. See sheets 3 and 4. There seems to be a slight difference in results - don't know why.

• ###### 7. Re: Week Starting on a Monday

Thank you, it worked.

The only thing I had to change was the week number equation.  It Saturday purchases were missing from the data. So I corrected it and it pulled without any issue.

int((datepart('dayofyear',[transactionDate]+[Year start move]))/8)+1

• ###### 8. Re: Week Starting on a Monday

I still can't believe it was so complicated...  Please check the result - I was a little concerned that the two methods looked to produce slightly different results

• ###### 9. Re: Week Starting on a Monday

Yeah, you would think there would be a simple way to set a fiscal week like you can do with the year. My numbers are matching the manual query I ran.

• ###### 10. Re: Week Starting on a Monday

Good - there is an entry in the ideas section, but I'm not sure it would have actually solved your issue with wanting Week 1 to start past the beginning of the year,