"While/Wend" or "Do Loop" Functionality in Anaplan
Within VBA, it is possible to create "Loop & Exit"/"While/Wend" or "Do Loop" functions. This allows formulae to continue running until an answer is produced.
Within Anaplan, there are workarounds, but none are truly as flexible, and all have their limitations.
Creating this functionality would close the gap on some of the final areas within excel that, as model builders, we still need to creatively workaround.
Very simple Excel Example:
Do While True
count = count+1
If count = 10 Then
This can be used in innumerable use cases, but as an example in call centre planning, to calculate capacity:
#Agents = number of reps available
Calls = IntCeiling(3600 / AHT)*#Agents
xAgent = Agents(SLA, Target Answer Time, Calls, AHT)
("Agents" as per the Anaplan formulae "Agents")
While (xAgent > #Agent) And (Calls > 0)
Calls = Calls - 1
CallCapacity = Calls
A relevant workaround is to create two modules to mimic a given number of "loops". These are not loops in Excel term, only line items in two modules having relevant formulas to replicate the loop process.
I usually works successfuly with 20 or 25 loops. It's enough to solve 90% of the issues. Most of the time the result given / achieved at loop between 7 to 10 will be the proper answer.
Loops 11 to 25 will only push forward the result given earlier with do decisive improvement of the result, and help to solve the remaining 10%.
You can customize the number of loops according to the size. For some model, 5 to 6 loops will be ok. For other models, 15 or 20. Ans so on. Feel free to experiment which number of loops are ok for you.
Yeah, this is exactly the approach we'd look to take, which is an absolutely fine workaround in the 95% of cases.
In terms of the way Anaplan's hyperblock technology operates, I understand my request is a long shot, but worth noting anyway!0
First module with 25 "loops" hard coded :
Second module also with 25 "loops" :
One "loop" consist of 2 sets of line items :
first module performs calculation.
Result1 is taken by second module which performs calculation.
Result 1 updated
First module takes the Result One updated in a second set of line item. Perfoms same caculation withn the second set of line item
Result2 is taken by second module which performs again its calculation in its own new set of line item .
Result 2 updated
And so on.
It takes a lot of line items because you need to replicate the sets of line item 25 times, but it works fine.
Loops are no loops in IT terms, they are a replication of identical line tems.
Line itel set 1
line intem set 2
line inte set 3...
each set of line item perform the same caculation as the previous set, but from an input which is the resut of the previous set.
The result of the current set of line item is the output, which will be used as an input for the next set of line item.
The two module configration alows for cross transfering of resut / data using Lookup and SUM. These are not mandatory in some cases.
Another vote for this functionality. This would be a great addition with so many uses. Could this possible get implemented like the goal seek functionality was?0
Get Started with Idea Exchange
See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!