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

u06gr8
u06gr8
in Integration and Extensibility Ideas

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

Tagged:
6
Up
6 votes

New · Last Updated

Comments

  • michel_maurel
    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

     

     

  • u06gr8
    u06gr8

    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!

  • michel_maurel
    michel_maurel

    First module with 25 "loops" hard coded :

    michel_maurel_0-1632743893093.png

     

    Second module also with 25 "loops" : 

    michel_maurel_1-1632743960819.png

     

    One "loop" consist of 2 sets of line items : 

    first module performs calculation.

    Result 1.

     

    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

    Resut 2

     

    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.

     

    Kind regards.

    Michel

     

     

     

  • wwood
    wwood

    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?

Get Started with Idea Exchange


See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!



Categories