Need help avoiding Circular Reference Error while using POST formula

NoahJ

I am working with a system that calculates what payments should be, then when the actual payment is made we record what that value and save it back into the model. Sometimes there are retroactive changes, which cause a variance between the calculated and the actual amount. We want to be able to post the variance to a future period so that it can be reconciled there. In the past that was entirely done manually, but I'm trying to do it with formulas.

 

I want to use the POST formula to allow business users to select a period to which the variance would be posted, and add all variances to be incorporated into the corresponding actual payment.

Based on the initial calculations (which flow top to bottom), if a payment of 659 is made in May as intended, it would show a variance of -167, as it does not incorporate the 'Variance Posted' amount. This is incorrect. However, when I try to set the 'Variance' line item to reference 'Calculated Payment including Posted Variance', it returns a Circular Reference Error.

 

Admittedly, the error does make sense, since depending on what values I allowed in the "Post Variance to Period" line, it would indeed cause a circular reference. But I as the builder know that I included logic limiting the 'Post Variance to Period' line item to be future periods - if a period the same or earlier than the specified month is entered, it does not flow into the Post line item. I even set the default offset amount for the POST formula to 1, so even if it is posting 0 it posts it to a future period. Since it is always adding to a later period, it could theoretically operate similar to using a PREVIOUS function which successfully avoids circular references... the math checks out, if only the system could be convinced to attempt it.

 

Does anybody know of a formula or method that would do the trick here? 

 

I tried using a SUM / TIMESUM / OFFSET, but it still gave the same error. I considered using the PREVIOUS function to always post it to the following month, but it would make for a messier final report if a change caused a variance multiple months back.

 

The only feasible workaround I can come up with at the moment is using an action to 'break' the circle - once the business users entered the necessary selections, they would run a 'Confirm Posting' action which would import the values to a new line item. However, that would decrease auditability and add complexity. Hopefully somebody can provide me the magic bullet!

ManjunathKN

Hi @NoahJ, 

have you tried LAG with parameter strict logic to make it one directional? Since your cir ref is happening on time, and I believe its not a synchronous one. if it does not work, I am not sure if there is any way out other than doing import.

Thanks,
Manjunath

NoahJ

Hi Manjunath, 

Thanks for sharing your thoughts!

I just tested it, and unfortunately LAG does not work - even when constrained to all one directional, it gives the 'Circular Reference' error. 

Additionally, POST and LAG work in opposite ways - POST effectively pushes a value to a future cell, adding up with all other values pushed to that same cell. Where as LAG (and OFFSET and similar functions) pulls values from a different period based on the offset amount, and can only be pointed to a single period. Sort of like the difference between a SUM and a LOOKUP. Even if it did not give an error, the business users would only be able to post the variance from a single period at a time: 

 

ManjunathKN

@NoahJ yes, i believe POST can go in either ways, in your case if POST and LAG acts in opposite way, you could try LEAD which also goes one directional for future values. 

 

If that doesn't help, I dont have anything.

 

Thanks,

Manjunath

ManjunathKN

@NoahJ  yes, if you are using POST then SUM or TIMESUM should help but as you mentioned that you already tested that. I am blank here.

Misbah

@NoahJ 

See if this helps

Note: Posted Month has to be populated for Time where it is to be posted  

Note: I have not calculated Initial Calculated Payment Amount line and Diff in Periods line. I hope Initial Calculated Payment line is not taking its values from any of the downstream line items

Thanks,

Misbah

Miz Logix

NoahJ

Hi Misbah, 

 

Thank you for commenting! That is a potentially helpful approach, and it may satisfy business needs, but the downside is that if there is an additional retroactive change that effects a month that has already had variance posted to it, it will not be able to compute the correct values. 

 

For example, in our test module we posted the variance in March and April to May and made the correct summed payment. If there is another retroactive change which now effects May and causes a new variance, your 'Final Variance' formula correctly accounts for that in the month of May - however, when we try to post the variance again, the formula takes the 'initial variance' value, which is incorrect.

 

That is why I need the posted variance to flow back into the original variance calculation, so that it can account for possible future changes. I wish I could guarantee that we would not have a change overlap a month that we post variance to, but I cannot.

NoahJ

Well, technically I figured out a solution... it's just one that I kind of hate and is not best practices for formula writing.

I realized that PREVIOUS works without circular reference error but had the limitation of only moving one month forward at a time... but stacking multiple PREVIOUS(PREVIOUS( etc. together removes that limitation. However, since our model time span is a year and changes could happen at any point along that, it would require stacking PREVIOUS all the way up to 11 instances (since our model lasts 1 year, and change could happen at any point)... it does not feel good, doing that much copying and pasting in a formula.

Here is my current working formula - to the end user, it will work exactly as desired. I started with the boolean of "is variance being posted to this month" so that it only runs through the rest of the IFs when it actually needs to.

@rob_marshallhow much will you judge me for putting this in an Anaplan model? 🤣