Circular Reference help

Hi,

The following formula below is giving me a Circular Reference when it should not be. Please advise on how to avoid this error. The LI that this Formula is in is Acutal and Projected 30 + $ Curve.

Pic

Formula

IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

Accepted answers

alexpavel

@neg177 you reference in the line-item 'Acutal and Projected 30 + $ Curve'.. the same line item, using the LOOKUP: 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M']

I presume that 'MOB -1M' is a previous month...Instead of LOOKUP you need to use PREVIOUS or OFFSET function... something like:

IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN

PREVIOUS('Acutal and Projected 30 + $ Curve') * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

All comments

obriegr

@neg177

There is a circular reference in your Then Statement. You are referencing back to the original line item you are working on (specifically 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] )

I believe you may have intended to use the 'Actual 30+ Dollars from Tableau' line item.

ChrisAHeathcote

@neg177

The formula is referencing itself;

Actual and Projected 30 + $ Curve = IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

Is this correct?

If so, then try and reference the source of Actual and Projected 30 + $ Curve and calculate the correct result in the true outcome.

Alternatively, create a new line item and calculate it there.

alexpavel

@neg177 you reference in the line-item 'Acutal and Projected 30 + $ Curve'.. the same line item, using the LOOKUP: 'Acutal and Projected 30 + $ Curve'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M']

I presume that 'MOB -1M' is a previous month...Instead of LOOKUP you need to use PREVIOUS or OFFSET function... something like:

IF 'Actual 30+ Dollars from Tableau' = 0 AND 'Actual 30+ Dollars from Tableau'[LOOKUP: 's54 MonthonBook Props'.'MOB -1M'] = 0 THEN

PREVIOUS('Acutal and Projected 30 + $ Curve') * 'Deliquency Curve Builder - Update Vintages When Necessary' ELSE 0

ChrisAHeathcote

Great spot. If the assumption regarding MOB-1M is correct in that it is designed to LOOKUP the the previous period then PREVIOUS would be the simplest option.
A great feature of PREVIOUS is that it does not generate a circular reference.

neg177

This is great advice thank you, but we are not looking to look up the PREVIOUS time period which the PREVIOUS function will do, we are trying to retrieve the PREVIOUS 'month on book' which is set in our columns as a List Item.

neg177

Below is a example of the Excel Formula we are trying to replicate.

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…