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

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 Date Channel Quantity 11/20/2018 Wholesale 1 11/9/2018 Retail 1 10/20/2018 Wholesale 1 10/31/2018 Retail 1 10/29/2018 Wholesale 1 11/2/2018 Retail 1 11/25/2018 Wholesale 1 11/23/2018 Wholesale 1 11/5/2018 Wholesale 1 10/31/2018 Retail 1 10/29/2018 Wholesale 1 9/1/2018 Wholesale 1 9/15/2018 Retail 1 9/30/2018 Wholesale 1 10/1/2018 Retail 1 10/15/2018 Wholesale 1 10/20/2018 Retail 1 11/1/2018 Retail 1

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

• ###### 1. Re: Use Max Date, dependent on a dimension, in Calculated Field to get rolling 4 week look back

Donna,

I'm not sure if this will work for your true setup,

but I had the feeling that it might benefit from a data scaffold of all possible dates.

Your data would be joined to the scaffold as shown below.

Then max retail date would be:

{ FIXED :MAX( IF [Channel]="Retail" THEN [Order Date] END )}

Can zero out all data past the maxRetailDate:

IF [Order Date]<=[MaxRetailDate] THEN [Quantity] END

I very likely didn't understand the nature of the rolling 4 week

with the max retail as an anchor.

I made an attempt, basically if the date isn't past the max retail date,

then add over a window of 28 days:

IF ATTR([Scaffold Date])<=ATTR([MaxRetailDate])

THEN

WINDOW_SUM(SUM([Include Qty Before Anchor]),-28,0)

END

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

1 of 1 people found this helpful
• ###### 2. Re: Use Max Date, dependent on a dimension, in Calculated Field to get rolling 4 week look back

THANK YOU SO MUCH!  The part that was holding me up was so simple that I can't believe I didn't get there on my own, but the first part of this solved for what I needed which allowed me to calculate the rolling period as I needed.  I REALLY REALLY appreciate your response!

• ###### 3. Re: Use Max Date, dependent on a dimension, in Calculated Field to get rolling 4 week look back

Donna,