Model Building - Avoiding Circular Reference

sagarkpr
sagarkpr
in Modeling

Working on a use case to reproduce following psuedo code in Anaplan

Start

Recommendation_given=0

for tranche 1 to 6
if tranche =1
then tranch_contribuiton = 0
else if tranch recommendation <= recommendation_given or tranch_recomendation = 0
then tranch_contribuiton = 0
else
tranch_contribution = tranch recommendation - recommendation given

Recommendation_given = Recommendation_given + tranch_contribution

loop

End

Tranche here is a list, when trying to implement this in Anaplan. I get the circular reference for calcualting Recommendation_given.

Below are line item calc

Tranch Recommendation is a input from another module.

Tranch Contribution = IF    Tranche Rank = Tranche #.'1'THEN    Tranch RecommendationELSE    IF        Tranch Recommendation = 0 OR Tranch Recommendation <= Recommendation_given    THEN        0    ELSE        Tranch Recommendation - Recommendation_given

Also attach is equivalent of excel

image.png

Let me know if I can share more information, Any direction appreciated.

Tagged:

Best Answer

  • AjayM
    AjayM
    Answer ✓

    Hi @sagarkpr ,

    One way to avoid circular reference is by using time dimension, and then converting it into 'tranche' list, which I have detailed below. If this doesn't help, hope you would get some other ideas in the community:

    1. Create a timerange of one year, assuming the tranche list will not have more than 52 items. You may increase number of years depending on number of list items in tranche list. Make sure you have total of all periods enabled for this time range
      image.png
    2. Create a sys module for this time range, to identify tranche list item for each item of time
    image.png
    image.png

    3. Create an input module by tranche list, where user inputs recommendation. I am using same module for showcasing output lineitems too. You may come back to 'STG' lineitems after completing step #4 below

    image.png


    4. Create a calculation module (circular reference avoiding step)

    image.png


    5. Finally your ouput would look as below

    image.png


    Cheers!

Answers

Categories