Refering to data in previous item of a list

I have pulled a list in a module and this list contains data for various events. Now I want that the calculation on any event in the list to act on the data on the previous list. Example: I have 3 line items in my module, namely the principal amount, increase rate (different for different time periods and events) and a final value. A list named Events has been pulled as a dimension in this module. Now I want that the first item in Events takes principal and rate values from another module and calculates the final value. The second item of the list to take this final value as its principal input and using rate calculate a final value; which will be used as the principal for third item in the list.... and so on. How can we achive this?

Best Answer

  • harish_bk
    Answer ✓
    Hi Peeush

    1) Then the other way would be to have the event list and time into one module
    2) Then line item previous event: Event List formatted
    3) Then give the values of previous event by each month
    4) Then write a look up on this value

    This is bit tedious but you can get both modules on a dashboard and simplify your work.

Answers

  • have you tried creating a property in the list which contains the previous item. You would have to manually create that, but that should be as simple as a one-off cut and paste. Then you can use that as a lookup to get to previous data.

    I have not modelled this, but cannot see why the approach wouldn't work.
  • Yes I had tried that, but it leads to a circular referencing when I try refering to values using Lookup.
  • HI Peeush,

    I can help you if you can share a screenshot of your bluepring view. That can ideally help us to get to find where the issue is. Ideally, the solution suggested earlier by Paul should not lead to circular reference.

    Regards
    Hari
  • Sorry for late reply. But will this attached snapshot work? (I have simplified the module to the bare minimum references outside this module.)
    I want to add the formula in "Sales After Event" as "Sales After Event [ lookup: Previous Event] + To be Added"...... but this gives as a circular reference error. Please suggest a solution.
  • [quote]Peeush GoelSorry for late reply. But will this attached snapshot work? (I have simplified the module to the bare minimum references outside this module.)
    I want to add the formula in "Sales After Event" as "Sales After Event [ lookup: Previous Event] + To be Added"...... but this gives as a circular reference error. Please suggest a solution.[/quote]
    HI Peeyush,

    Did you try using CUMULATE formula?
  • Yes, I did that as well. It did not work. Apparantly the cumulate can only act if we store the result in some other third line item. Not in the same circle of reference.
  • Hi Peeush

    Since all the values are interlinked its really tough to do it without a circular reference.

    The way i can think of is:

    Keep a drop down line item which says which value is result, then have  drop down with what is to be caculated then write if conditions for all the three line items, and all the three line items should have option of manual value and calculated value (either of this value will move to final value based on selection parameters again).

    Its big long procedure but this will surely work without any circular references.
  • [quote]Harish B KHi Peeush

    Since all the values are interlinked its really tough to do it without a circular reference.

    The way i can think of is:

    Keep a drop down line item which says which value is result, then have  drop down with what is to be caculated then write if conditions for all the three line items, and all the three line items should have option of manual value and calculated value (either of this value will move to final value based on selection parameters again).

    Its big long procedure but this will surely work without any circular references.
    [/quote]Sorry Harish, but I did not get that procedure.
    Also AFAIK, the line item can only have either a calculated value or a manual value (after enterring an IF the value turns calculated).

    Please clarify!

    Thanking You,
    Peeush 
  • Hi Peeyush yes once you have written an if condition the lineitem cannot be used for input purpose,

    Hence have one line item for input purpose, have one line item for the if condition.

    Have final line item to specify if this should take input value or a calculated value.

    For example:

    1) 2% Rate, Principal is 2000, interest 40 would be calculated
    2) We know that interest is 80, rate is 2% which are input now so the system should calculate principal 4000 and publish for you


    Line items:

    1) To be calculated Member: Values would be "Int, Rt, Pri" - These are list members used for calculation purpose and format is list
    2) Rate Input
    3) Rate Calculated: Here link the Int Input and Princiapl Input
    4) Rate Final : If to be calculated member is "Rt" then Rate Calculated else Rate Input

    Similarly have line items for Interest and Prinicipal too

    Hope this should solve your issue

    Cheers

    Harish B K
  • Hi Peeush

    Seems I got your requirement now:

    1) Create a property previous event in the event list
    2) Make the property list formatted with the value events
    3) Put the property value as the previous events (Source event)
    4) Write a lookup formula to pull in the value in the previous event

    Its almost similar to what you have done, but rather than referring the same line item for lookup purpose you would be checking from the property value and then retreive the amount i guess
  • [quote]Harish B KHi Peeush

    Seems I got your requirement now:

    1) Create a property previous event in the event list
    2) Make the property list formatted with the value events
    3) Put the property value as the previous events (Source event)
    4) Write a lookup formula to pull in the value in the previous event

    Its almost similar to what you have done, but rather than referring the same line item for lookup purpose you would be checking from the property value and then retreive the amount i guess
    [/quote]

    This won't work as the value in Line Item property will not vary with the timescale of anaplan. 😞
  • I have a similar problem, and I'm very keen to know how to solve the circular problem....

  • Pranjal
    edited May 2023

    ..