Circular Reference help

neg177
Regular Contributor

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

neg177_0-1629397209882.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
alexpavel
Certified Master Anaplanner

Re: Circular Reference help

@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 -1Mis 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

 

 

 

 

View solution in original post

6 REPLIES 6
obriegr
Certified Master Anaplanner

Re: Circular Reference help

@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.
ChrisHeathcote
Community Boss

Re: Circular Reference help

@neg177 

The formula is referencing itself;

 

Actual and Projected 30 + $ CurveIF '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. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
alexpavel
Certified Master Anaplanner

Re: Circular Reference help

@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 -1Mis 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

 

 

 

 

View solution in original post

ChrisHeathcote
Community Boss

Re: Circular Reference help

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.
Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
neg177
Regular Contributor

Re: Circular Reference help

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
Regular Contributor

Re: Circular Reference help

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

neg177_0-1629478203824.png