3 Replies Latest reply on Dec 3, 2018 6:35 PM by swaroop.gantela

    Use Max Date, dependent on a dimension, in Calculated Field to get rolling 4 week look back

    Donna Skillman

      Hoping someone can help me!!

       

      I need to use a Max Date to calculate a 4 week rolling trend.  The issue is that the Max Date only applies to one set of the data. Due to how we get our data feeds, our Retail Data is 2 weeks behind our Wholesale Data so I need to start the rolling 4 week look back at the Max Date for when  the Channel is Retail, but the trend needs to look at all data going back from that anchor date.  See data example here:

       

        

      Order DateChannelQuantity
      11/20/2018Wholesale1
      11/9/2018Retail1
      10/20/2018Wholesale1
      10/31/2018Retail1
      10/29/2018Wholesale1
      11/2/2018Retail1
      11/25/2018Wholesale1
      11/23/2018Wholesale1
      11/5/2018Wholesale1
      10/31/2018Retail1
      10/29/2018Wholesale1
      9/1/2018Wholesale1
      9/15/2018Retail1
      9/30/2018Wholesale1
      10/1/2018Retail1
      10/15/2018Wholesale1
      10/20/2018Retail1
      11/1/2018Retail1

       

       

      The latest Retail Order Date here is 11/9/2018.  I need to set that as my anchor and then look back at all data from that date. I need to set this looking at the previous rolling 4 weeks to the prior rolling 4 weeks, using that anchor date as my starting point.

       

      I have read so many articles and threads and tried literally dozens of formulas and none seem to do what I need.  Short of duplicating the data set, I am not sure what to do.  I don't want to use custom SQL if I can avoid it.

       

      Thank you for ANY help!

       

      Donna