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

    Jason Kim

      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:

       

      Screen Shot 2019-04-22 at 8.56.41 AM.png

      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!