10 Replies Latest reply on Jan 10, 2019 3:13 AM by Ade Ogunbufunmi

    Average for same weekday past 4 weeks?

    Maximus Decimus Meridius

      I want to compare the value of any given day - say a Sunday - and compare it to the average value of the 4 past Sundays (or whatever day).

       

      What I'm hoping is to flatten out these trend lines (which show totals), so I can more clearly see when something deviates from the average value for that weekday:

       

      So here when there's a dip, I want to understand if it's an unusual dip for that weekday or if it's to be expected. I essentially want to remove the day of week effect from this data - the past 4 weeks of data form the same weekday is the first approach I thought of (and I figure it might be the easiest) but I'm interested in hearing other solutions as well.

        • 1. Re: Average for same weekday past 4 weeks?
          Emily Le Coz

          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

            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.

            • 3. Re: Average for same weekday past 4 weeks?
              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

                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

                  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

                    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

                      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

                        I know this is an old-ish 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)

                        2 of 2 people found this helpful
                        • 9. Re: Average for same weekday past 4 weeks?
                          Joe Oppelt

                          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

                            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