Returning same value from same Line Item but different items
Hi all,
Is it possible to return a value from an item on the same line item? For example, picture this as my module:
I want to be able to calculate a delta between S1 and S2 or S3 and S1, like this:
Here I'm using SELECT but this will be a lookup since I want the user to be able to select which scenarios he want to compare, of course this formula won't work because this is a circular reference, is there a way to make it work?
The user wants to have a Delta on the same table as the scenarios, that's why I'm not creating a second module to calculate it.
Answers
-
@PauloM unlikely that you can accomplish this on one line item because there are only a limited number of aggregation/disaggregation rules from child to parent. But there's an alternative.. Using separate line items to calculate all the variances mitigates the need for nested IF statements and allows you to use conditional formatting on the original line item. So while you won't see the variance at the top level you can see a color that indicates a variance. The other line items will provide you all the variances.
Also, to traverse list items and avoid the circular reference you can try using CUMULATE.
Even if you could find a creative way to accomplish this I wouldn't recommend it. You'll likely have to break a number of Planual rules.
0 -
To avoid circular reference, you need to stage the calculations to a different line item. In this case, you can create another line item (Sales Staging) and calculate / fetch the values for Scenario 1, 2 and 3 and then in your main line item you can refer the stage calculations. here is how it works.
0
