Forum Discussion

P0ach3r1's avatar
P0ach3r1
Frequent Visitor
3 years ago
Solved

circular dependency - need help on formula

Hi,   I'm trying to write a formula in a column and am getting circular dependency error.  I want it to return red, amber or green depending on value in abs_Variance column.  I'm using abs to get a...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi P0ach3r1 


    Can we maybe first try to simplify the formulas? I do not see the tables but I think the formula could also be written like this.

     

    I assume that you crete the calcualted column in the same table where you also store all the columns you refer to, right?

     

    Please build first this column

     

    Abs_Variance =
ABS (
    IFERROR (
        DIVIDE (
           [Actual/Projected] - [Client Forecast],
           [Actual/Projected]
        ),
        0
    )
)

     

     

    and then this

     

    RAG =
IF (
    [Abs_Variance] < 0.05,
    "Green",
    IF (
        [Abs_Variance] < 0.01,
        "Amber",
        "Red"
    )
)

     

    Please tell me what happens.

     

    Best regards

    Michael

    -----------------------------------------------------

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

     

Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Hi P0ach3r1 


Can we maybe first try to simplify the formulas? I do not see the tables but I think the formula could also be written like this.

 

I assume that you crete the calcualted column in the same table where you also store all the columns you refer to, right?

 

Please build first this column

 

Abs_Variance =
ABS (
    IFERROR (
        DIVIDE (
           [Actual/Projected] - [Client Forecast],
           [Actual/Projected]
        ),
        0
    )
)

 

 

and then this

 

RAG =
IF (
    [Abs_Variance] < 0.05,
    "Green",
    IF (
        [Abs_Variance] < 0.01,
        "Amber",
        "Red"
    )
)

 

Please tell me what happens.

 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

 

grantsamborn's avatar
grantsamborn
Icon for Solution Sage rankSolution Sage
3 years ago

Wouldn't using DIVIDE's 3rd parameter accomplish the same thing?

Abs_Variance =
ABS (
    DIVIDE (
       [Actual/Projected] - [Client Forecast],
       [Actual/Projected],
	   0
    ),
)

 

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    the same like what? I only rewrited your formula. Could you please show the results and say whether it goes in the right direction? 🙂

    • grantsamborn's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      3 years ago

      The 3rd argument safely handles division by zero.  Is there some other type of error that you are trying to handle?

      (I'm only asking this to confirm whether or not what I've learned is correct.)