"While/Wend" or "Do Loop" Functionality in Anaplan

u06gr8

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

Exit Do

End If

Loop

 

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
Wend

CallCapacity = Calls

michel_maurel

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.

 

Kind regards.

Michel