Reverse Cumulate Alternative?

Certified Master Anaplanner

Reverse Cumulate Alternative?

Hi - hoping to see if anyone has a solution to this:

In the following example, I'm trying to calculate (going backwards) when the sum (less current period) would be greater than the test doh line item. 

In Jan 20, it would look back 3 periods to be greater than or equal to 92. (Oct/Nov/Dec)

In Mar 20, it would look back 4 periods to be greater than or equal to 92. (Nov, Dec, Jan, Feb)

 

Capture.PNG

I have tried various forms of CUMULATE, OFFSET, PREVIOUS... but haven't been able to create this logic.

Has anyone had success performing a reverse recursion?

 

Thanks in advance!

Jack

3 REPLIES 3
Community Boss

Re: Reverse Cumulate Alternative?

one solution is: test all possibilities with a movingsum and a numbered list:

nathan_rudman_0-1608279156839.png

 

extracting the result  with first non blank:

 

nathan_rudman_1-1608279185931.png

here's the list of possibilities:

nathan_rudman_2-1608279276278.png

 

there might be an easier way if we are really talking about calendar days and simple numbers.

but this solution will work for anything.


Nathan Rudman, Anaplan Model Builder
Occasional Contributor

Re: Reverse Cumulate Alternative?

Looks like a workable solution. thanks

Certified Master Anaplanner

Re: Reverse Cumulate Alternative?

You could use that start date and period() function to find which month satisfies the 92 days prior. using a month period line item your formula would be Period(start() - test doh)  from here you should easily be able to calculate how many months to look back.