2 Replies Latest reply on Mar 6, 2017 12:16 PM by Ginger Macfarlan

    Datediff with Max Date

    Ginger Macfarlan

      My data has a list of week ending dates and the corresponding sales for that week. When analyzing this data, I look at rolling week aggregates (i.e., the most recent 52 weeks vs the most recent 52 weeks from a year ago)--the point here being that calendar year isn't helpful to what I need.

       

      What I'm trying to do is create a This Year/Last Year flag from a calculated field, so that I can "bucket" sales accordingly into L52 and LY52. I can get pretty close with this calculated field:

       

      IF DATEDIFF("week",[Week Ending Date],TODAY())>51 THEN "LY"

      ELSEIF  DATEDIFF("week",[Week Ending Date],TODAY())<52 THEN "TY"

      END

       

      This calculation isn't totally correct because my most recent data refresh was a few weeks ago, so the most recent date doesn't align with today's date. I can get correct results by manually creating a "Makedate" for the most recent week in the data, instead of using the "Today" function, but obviously I would like my This Year/Last Year flag calculation to be automated.

       

      Ideally, I need the DATEDIFF to be calculated using the Max Date in the data. However, when I try to use "MAX (Week Ending Date)", I get that "cannot mix aggregate and non-aggregate arguments with this function." I tried wrapping my Week Ending Date in an ATTR, which sort of worked, but only returned "TY" results.

       

      I'm attaching a packaged workbook with sample data to illustrate my problem. Thanks in advance!