About JohnP

Hi @LouiseDepaquis,   I'm afraid I don't know how far you are through Level 2, so I hope the below reflects what's in your model. I agree that the issue arises from the reference to 'Beginning Inventory' (a good way to identify the problematic part of a formula is to try out each part of the formula individually i.e. if you just make the formula for 'Suggested Order Amount for the Month' equal to 'Beginning Inventory', you will get the same circularity error). The likely reason that 'Beginning Inventory' is causing issues is that it contains PREVIOUS(Ending Inventory) and 'Ending Inventory' references 'Confirm Purchase Order Receipt'. 'Confirm Purchase Order Receipt' is the problem, as the formula contains POST(Final Shipment Amount, Final Shipping Time Weeks). The POST function can actually take a negative number as its second argument (the number of periods to offset the value in the first argument), which means it then posts the value into the past instead of the future. So if 'Final Shipping Time Weeks' is negative, the function posts the value for 'Final Shipment Amount' into the past.  This is a problem as the formula for 'Final Shipment Amount' references 'Suggested Order Amount for the Month' so, if somehow the 'Final Shipping Time Weeks' is a negative number (in this example -1), the formula references result in the below circular reference: As I mentioned above, the formula for 'Confirm Purchase Order Receipt' contains POST(Final Shipment Amount, Final Shipping Time Weeks). Naturally, in practice it's impossible to have a negative shipping time however, as the second argument refers to a line item (which could return a negative value), the calculation engine cannot exclude the possibility that the second argument in POST might be negative and thus result in circularity, so it throws the circularity error that you have seen. (In this particular example, the circularity does not occur when a positive value is used as the offset instead of 'Final Shipping Time Weeks' in the formula for 'Confirm Purchase Order Receipt', e.g. if the formula contains POST(Final Shipment Amount, 2) instead, 'Suggested Order Amount for the Month' can reference 'Beginning Inventory' without any circularity occurring.)   This is my understanding of it, but if anyone spots any mistakes please jump in and correct me! Hope this helps  🙂   Best, John     ... View more

Hi @tobiasgysel,   This is an interesting question. I've put my first attempt below, but there are probably much easier (and more efficient) ways.   Firstly you start with a list of your players e.g. ‘List 1’ with a code like the one below (i.e. a code with a unique numeric component): You then make an identical duplicate of this list, e.g. ‘Duplicate List 1’. This list should contain identical items with identical codes; the unique numeric component should match that of the same item in the original list. (If you were to follow best practice, the duplicate code wouldn't be the same as the original code but in this instance it shouldn't matter too much. Create a system module for each list and include a line item that converts the numeric part of the code into a value:   Create an output module the two lists as the dimensions and three line items: Valid Pair: this is a Boolean-formatted line item which removes pairings of the same item and duplicate pairings, formula: 'SYS01 List 1 Details'.Value <> 'SYS02 Duplicate List 1 Details'.Value AND 'SYS01 List 1 Details'.Value < 'SYS02 Duplicate List 1 Details'.Value . (If the numeric components of the codes are not the same in both lists, this will remove the wrong pairings.) Pair Name: text-formatted line item that creates a name for the pair, formula: NAME(ITEM('List 1')) & " & " & NAME(ITEM('Duplicate List 1')) Pair Code: text-formatted line item that creates a code for each pair, formula: CODE(ITEM('List 1')) & "." & CODE(ITEM('Duplicate List 1')) Then create a saved view showing the list of pairs with the pair name & code line items, filtered by 'Valid Pair':  Run an import to create a list of pairs using the above saved view to populate the name and code. You should be able to reference the original player items by breaking down the pair code. This should work fine for smallish lists but obviously might cause problems with larger lists as the output module size is proportionate to the number of list items squared.   Hope this helps!   Best,  John ... View more