The difficulty of the using week is that the start changes year by year. I would presume that you want to compare a Saturday to a Saturday?
Hi Alex Kerin,
That would be the idea. I assumed that the 'Week' function starts each year on the same day of the year and counts up 7 days. So Week 41 would always have Day 1 on a Sunday and Day 7 on a Saturday. If I then compare Week 41 in 2012 to Week 41 in 2011, both Day 1's would be Sundays.
If that is not correct, then I will need another approach, as indeed the idea is to compare Day to Day over years given that variances in Sales can occur on weekends and the like.
No, Week 1 starts on the 1st of Jan, so week 1 may only have one day in it (Saturday). For example, building on your equation (replacing today() with a parameter and [Sales] with the date so we can test):
if datepart('weekday',[Order Date])=datepart('weekday',[Order Date Parameter])
and datepart('week',[Order Date])=datepart('week',[Order Date Parameter])
and datediff('year',[Order Date],[Order Date Parameter])=1 then [Order Date] end
looks like it works until you choose the 3rd Jan of 2012, which is a Tuesday. Week 1 of 2011 only has a Saturday, no Tuesday, so the calculation returns null.
You want to compare the first Saturday of the year to the first Saturday of last year. That's more complex as we have to create our own week definition. Thinking about that....
Thinking about it algorithmically, we want to do:
1. MIN(DATEPART('day', [dtDate]) WHERE day of the week = 'Sunday') As Week 1, Day 1 As START
2. WEEK/DAY = TODAY() - (TODAY() - START %7)
At least I think that makes sense:
Find the first Sunday of the year and that counts as 1/1. Take today and minus the date of 1/1, divide by 7 to find the week number (rounded down) and modular 7 to find the day of the week...
Yes - this is very similar to this issue: http://community.tableau.com/thread/121143
But I never worked out the mod version. I am now travelling for the rest of the week, so hopefully this will be at least a start for you.