
1. Re: Average for same weekday past 4 weeks?
Emily Le Coz May 31, 2016 2:08 PM (in response to Maximus Decimus Meridius)Maybe you can create a Day of the Week column in your Excel spreadsheet (or whatever your using) and then drag that pill into the filter shelf (and into the line chart). Then show the filter and filter on Sunday or whatever day of the week you're interested in seeing.

2. Re: Average for same weekday past 4 weeks?
Joe Oppelt May 31, 2016 3:30 PM (in response to Maximus Decimus Meridius)I'd work up a calc that does something like this:
(sum([Sales]) +
lookup(sum([Sales]),7) +
lookup(sum([Sales]),14) +
lookup(sum([Sales]),21)) / 4
This grabs the current sum(sales) and the three sums on the prior three weeks.
In the attached workbook I did it in two steps, adding the 4 values, and then another calc to divide by 4, but you can actually combine it into one calc.
Attached is an 8.2 workbook, but it'll upgrade to any version.
superstore  4week average.twbx 993.5 KB


3. Re: Average for same weekday past 4 weeks?
Joe Oppelt May 31, 2016 3:30 PM (in response to Joe Oppelt)There might be a more elegant way to do this, but if you're always going to go back 4 weeks, this will do the trick.

4. Re: Average for same weekday past 4 weeks?
Maximus Decimus Meridius Jun 1, 2016 12:44 PM (in response to Joe Oppelt)Thanks. I'm realizing the challenge I'm running in to is that dates are listed multiple times, with one listing per call center:
so going strictly by rownumbers does not work for me. I know there's partitioning but not 100% clear of how that would work. Basically, i want to calculate the average for the same call center on the same weekday

5. Re: Average for same weekday past 4 weeks?
Emily Le Coz Jun 1, 2016 12:52 PM (in response to Maximus Decimus Meridius)Could you also filter by call center? So that only one call center appears at a time on your graph?

6. Re: Average for same weekday past 4 weeks?
Joe Oppelt Jun 1, 2016 1:17 PM (in response to Maximus Decimus Meridius)OK, so now it depends on what you want to see on your sheet. You may already have some of the partitioning taken care of based on what you need there.
Post a sample workbook (or an approximation of it built on a couple of months of bogus data in an excel sheet. Maybe just 3 or 4 call centers.) I'll work with you on that. Specify your tableau version so I can work in the same one you are using.

7. Re: Average for same weekday past 4 weeks?
Maximus Decimus Meridius Jun 2, 2016 5:32 PM (in response to Joe Oppelt)Hey Joe, I started a new thread with the workbook attached here. Still haven't worked out how to do this...thanks

8. Re: Average for same weekday past 4 weeks?
David Uslan May 1, 2017 1:17 PM (in response to Maximus Decimus Meridius)2 of 2 people found this helpfulI know this is an oldish thread, but I was trying to do this as well and couldn't find a good solution online.
I wanted to compare yesterday's number of spenders to the previous four week average for the same day of week.
The hardest part for me was calculating the four week average, which I solved by doing creating a calculated field containing this:
IF DATEPART('dayofyear',[Created Day]) = DATEPART('dayofyear',TODAY())8
OR DATEPART('dayofyear',[Created Day]) = DATEPART('dayofyear',TODAY())15
OR DATEPART('dayofyear',[Created Day]) = DATEPART('dayofyear',TODAY())22
OR DATEPART('dayofyear',[Created Day]) = DATEPART('dayofyear',TODAY())29
THEN [Daily Spenders]
ELSE NULL
END
Then yesterday's:
IF DATEPART('dayofyear',[Created Day]) = DATEPART('dayofyear',TODAY())1
THEN [Daily Spenders]
ELSE NULL
END
Then just a final field with (for some reason i couldn't use SUM(), but MAX got me the correct result, so I just left it):
MAX([Yesterday]) / [Average Last 4 Weekdays]
I also have a filter to ensure I'm only looking at the past 30 days of data, otherwise this would pick up previous year's data too.
The only problem I foresee with my solution is when the new year comes around  hopefully Tableau is smart enough to wrap the dayofyear calculation around instead of creating negative numbers (e.g. hopefully Jan, 1st  8 becomes 358 instead of 7)

9. Re: Average for same weekday past 4 weeks?
Joe Oppelt May 1, 2017 1:32 PM (in response to David Uslan)David Uslan wrote:
...
The only problem I foresee with my solution is when the new year comes around  hopefully Tableau is smart enough to wrap the dayofyear calculation around instead of creating negative numbers (e.g. hopefully Jan, 1st  8 becomes 358 instead of 7)
I haven't played with dayofyear in this respect, but Tableau certainly handles dateadd backwards into the prior year accurately.

10. Re: Average for same weekday past 4 weeks?
Ade Ogunbufunmi Jan 10, 2019 3:13 AM (in response to Joe Oppelt)Hi David,
I managed to create something to expand on your original solution, as I was facing the issue of wrong calculations once 2019 started.
It not the cleanest solution but should help people who are faced with this issue, so i used dateadd formula to turn yesterday into same date last year and then add the relevant number of days to create the same cycle look back.
IF DATEPART('dayofyear',[Created Day] = IF DATEPART('dayofyear',TODAY()) 8 < 1 then
DATEPART('dayofyear',DATEADD('year', 1, TODAY()1) + 358)
ELSE DATEPART('dayofyear',TODAY()) 8 END
OR DATEPART('dayofyear',[Created Day]) = IF DATEPART('dayofyear',TODAY()) 15 < 1 then
DATEPART('dayofyear',DATEADD('year', 1, TODAY()1) + 351)
ELSE DATEPART('dayofyear',TODAY()) 15 END
OR DATEPART('dayofyear',[Created Day]) = IF DATEPART('dayofyear',TODAY()) 22 < 1 then
DATEPART('dayofyear',DATEADD('year', 1, TODAY()1) + 344)
ELSE DATEPART('dayofyear',TODAY()) 22 END
OR DATEPART('dayofyear',[Created Day]) = IF DATEPART('dayofyear',TODAY()) 29 < 1 then
DATEPART('dayofyear',DATEADD('year', 1, TODAY()1) + 337)
ELSE DATEPART('dayofyear',TODAY()) 29 END
THEN [Daily Spenders]
ELSE NULL
END
Thanks