3 Replies Latest reply on Apr 25, 2019 7:29 AM by ShivaRam Chennapragada

# Comparison of Same Period Last Year by Day of the Week

Hi there,

I think I'm very close to the solution, but I can't quite connect the dots.

I am creating a view which compares the period from yesterday to the last 28 days this year vs. the same period last year. The approach I used to do this is similar to the one found here which uses a filter to label the current period from the last period:

```CASE [DATE_DAY] < DATETRUNC('day', TODAY())
WHEN [DATE_DAY] <= DATEADD('day', -1, DATETRUNC('day', TODAY())) AND [DATE_DAY] >= DATEADD('day', -29, DATETRUNC('day', TODAY())) THEN "Last 28 Days"
WHEN [DATE_DAY] <= DATEADD('day', -366, DATETRUNC('day', TODAY())) AND [DATE_DAY] >= DATEADD('day', -394, DATETRUNC('day', TODAY())) THEN "Last 28 Days - Last Year"
END
```

I then wish to plot a line graph which shows the daily trend of the current period vs. last period YOY.

I approached this first by using DATEPART('dayofweek', [DATE]) as a discrete dimension which works fairly well:

But due to the shift in days when the calendar year changes, the lines are mis-aligned since for example 04/20/2019 (a Saturday) is compared against 04/20/2018 (a Friday). (FYI The valleys in the plot are weekends which explains why there is a repeating pattern).

I then tried to modify the approach below for my purposes, but I can't quite connect the dots and my brain is fried from thinking about this too long.

Re: The same day last year comp

The approach above is for specific dates, but not for relative ranges that are rolling dates. I need to use relative dates rather than date parameters since this is a daily report with data loading every day.

I'm not able to provide real data since it's proprietary, but I re-created the logic of what I've done so far in Superstore attached (again, I can't use parameters in my actual use case).

Any help would be MUCH appreciated!

• ###### 1. Re: Comparison of Same Period Last Year by Day of the Week

Jason- Are you trying to compare Weekdays to Weekdays for example - Monday of this year to a Monday of last year? Then you'll have to change your calc to

change 366 to 364 to account for day shifts.

Hope this helps.

Thanks,

Shiva.

• ###### 2. Re: Comparison of Same Period Last Year by Day of the Week

Hi Shiva,

Yes, mostly that is what I want to do.

I am comparing for example yesterday which was a Sunday (April 21, 2019) to the "same" Sunday of April of last year (April 22, 2018), and each of the 28 days preceding yesterday to the same period last year.

Right now, my view in the line graph plots each line so that they match the exact days (e.g. 04/21/19 vs 04/21/18). Put differently, this view shows the 111th day of 2019 vs. the 111th day of 2018. Instead I need it to compare the 111th day of 2019 to the 112nd day of 2018.

Changing -366 to -364 as you suggest above doesn't seem to help with this unless I'm doing something wrong? Does what I'm trying to do make sense?

• ###### 3. Re: Comparison of Same Period Last Year by Day of the Week

Hi Jason,

Sorry for late response. We use these metrics everyday at work, we call them "Prior Year Same Day (PYSD)" measures, although these are calculated in database in SQL but the logic is similar in Tableau. Have you also changed the ending date(the range should be between 364 and 392 to account 28 days)?

Thanks,

Shiva.