Avoiding circular references with calculations on Previous List Item (Numbered List)

chris_liassides

Hello community members! 

 

I am facing an issue which i cannot seem to find a solution to, but that someone might be able to help! This particular exercise started as a way to deal with massive amounts of sparsity in a model used by my employer. The business has a dimension (monthly vintages) representing monthly loan originations. The current approach was to use this as a dimension, alongside Time and a few others, which results in huge amounts of sparsity, as for example, a future loan originated in say Jan 21, still gets provisioned for all items on the time scale the module utilises, even if by definition there is no valid calculation to take place.

I have thought of a way to deal with this using what i would be doing in "excel logic". Basically i figured out a way to calculate the valid intersections of vintages and time, and created a numbered list with the relevant number of intersections. 

 

The problem starts once i try to calculate line items that depend on a previous list member. Anaplan is not able to tell that there is no circularity in the logic (even though the same calculation works just fine when performed in a module with the time dimension). I think this is the same problem discussed here (https://community.anaplan.com/t5/Anaplan-Platform-Discussions/PREVIOUSITEM-PREVIOUS-on-a-list/td-p/24575), but i was unable to apply any of the solutions there (maybe i am just missing something?). 

 

The line items and logic is as follows: 

Good Book - Opening Receivables: Prior List member's closing receivables

New Advances: Linked to input modules

Good Book Payments: Based on an input % of (opening receivables + new advances)

Good book Interest charge: Based on an input % of (opening receivables + new advances)

Flow to Bad Book: Based on an input % of the prior List Member's Good Book's Closing Receivables

Good Book Closing Receivables: Summation of the items above. 

 

I can provide blue print and grid views if required. Would very much appreciate any tips or possible solutions to try out. 

bram.kurstjens

I would not suggest creating a combined hierarchy with time in it, I would rather combine the other lists if possible, but if you must then this might be a solution:

 

1 Create a time subset that includes the maximum amount of periods that you would need for any combination.

2 Make sure that your combined hierarchy has some logic to know the order and what is the start (e.g. a property that starts at 0 and adds 1 for each new list item

3 Map your hierarchy to the time subset (e.g. startdate + property rank)

4 Create a module that has the time subset with your previous formulas

5 Link the new module with the combined hierarchy module with the link you created in step 3.

 

 

If you are interested in this solution, but cannot seem to get it done, please let me know, then I'll build an example.

 

 

chris_liassides

Hi Bram - many thanks for the quick response! I think this is what one of the suggestions in the thread i linked to was alluding. 

 

I can do Step 1 and 2 (Code property is a number starting from 0 to n possible intersections). I am not sure how to "map" the hierarchy with the time subset, is this done via a line item formatted with time? Or do i need to add the numbered list dimension to the line item (apologies for the basic question here!)? The example would probably help but keen to try it out before asking you to build one! 

 

 

bram.kurstjens

Ok, I've built it anyway to make sure everyone is helped out on this topic. Again would not say that this is best practice:

 

First you need to create a time range just for this purpose. I've put it to 1 period (1 year) in the year 2030, any year will do. This will allow your calculation module (mentioned later) to have 365 intersects if you put it to 'days' or 12 if you put it to 'months'. If you need something in between or more then of course you can extend the period.

The first module you need is one that creates all the intersects you need, in my case between product and month, I called it the hierarchy combination module:

Import this filtered view into your combined hierarchy list, in my case called "Product - Month',. with Order as property. 

Now create your "Final Module" the one that takes user input, in my case directly, of course you can apply formulas there as well. Pay special attention to the 'Date mapping' field, that will be useful later: DATE(2030, 1, 1) + 'Product - Month'.Order - 1 . Minus 1 so that we start at the first date, 2030-1-1 was the first date of my time range. If you have set the period to months, then this field should not be date, but time period month and the formula should be PERIOD(DATE(2030, 1, 1))  + 'Product - Month'.Order - 1. In your case the formulas for Good Book and Good Book closing receivables should still be empty.

The next step is to create a calculation module that uses the time range. You could for size reasons only include the first and the last item (with the last item doing all the "SUM: Final Module.Date Mapping" that happen above), but to keep it as simple as possible, I chose to create every line item seperately.

Finally add the two formula's in the final module that reference to Good Book and Good Book after closing receivables with a lookup on the Date Mapping.

 

 

I hope that clarifies it.

 

KR,

 

Bram Kurstjens

chris_liassides

Hi Bram - thank you again for the reply. I started to do this but realised that it probably wont work for the usecase here.  The user wants the definition of the intersections to be "version" independent (referring to generic list with dummy versions rather than the official "VERSIONS"). 

 

For each version they want to be able to define it as such:

The Line Item "Valid Months & Vintage Instances" calculates the number of intersections based on the inputs. I had created a secondary module which then worked out and allocated to each one of the instances the correct Vintage, month on book, and ultimately calendar month, all of these being version dependent on the original inputs of the user. 

 

 

I then wanted to be able to do the calculations, which forced me to deal with the fact that Anaplan doesn't like using "previous" items in its calculations if the dimension is not "time". 

 

Correct me if i am wrong but if i use the proposed methodology to solve the circular reference here would restrict me to "one" version as that needs to be loaded in the list properties? If i understood it correctly then i would not be able to use this and would need an alternative solution (if one does exist!). 

bram.kurstjens

Hi Chris,

The idea is that you can map your hierarchy to a time dimension and then use the functions available for the time hierarchy (previous()) and then map it back to your original hierarchy. This should be possible in any situation I think, but in your case it is probably easier to discuss this directly. I'll send a PM.

chris_liassides

Thank you. I will await your message! Thanks for your time thus far - much appreciated!