Betreff: Columns - Nested If Statement

[-Ray]

Hi,

I'm trying to create one giant if statement for a custom column, pretty new to Workfront so excuse me if I'm far off with my staring point.

My problem is that Workfront doesn't allow customization of fields for the Risk area of a project, so we wanted to create columns for a custom view of a typical Risk Matrix using the 4x4 method.

So far, I have created two columns, A and B which intake currency and double integers. Last step is to create column C that provides the risk score based on answers in column A and B. So far I have what I believe are the basics which will be repeating a total of 16 times. I know I've messed up somewhere, and am hoping someone can point me in the right direction. 

valueexpression=IF({"probability"}<25,IF({mitigationCost}<5000,"1","")),IF({"probability"}<25,IF({mitigationCost}=5001-10000,"2","")),IF({"probability"}<25,IF({mitigationCost}=10001-15000,"3","")),IF({"probability"}<25,IF({mitigationCost}>15000,"3",""))

[lgaertner]

Hi,

why did you put probability into quotes?

In each step you are checking if probability < 25, why don't you try nesting like this:

IF({ "probability"} < 25, 
    IF({ mitigationCost } < 5000, 
        "1", 
        IF({ mitigationCost } = 5001 - 10000, 
        "2", 
        IF({ mitigationCost } = 10001 - 15000, 
        "3", 
        IF({ mitigationCost } > 15000, 
        "3", 
        ""))))
    )

I did not test it, but hopefully you do understand, what I mean.

Regards

Lars

[Doug_Den_Hoed__AtAppStore · Accepted solution]

Hi @-Ray,

Although untested, I’d suggest this pattern (dropping the quotes around probability, using < to handle ranges, and returning numbers vs strings for sorting purposes): 

valueexpression=IF({probability}<25,IF({mitigationCost}<5000,1),IF({mitigationCost}<10000,2,IF({mitigationCost}<15000,3,4))),IF({probability}<50,IF({mitigationCost}<5000,5),IF({mitigationCost}<10000,6,IF({mitigationCost}<15000,7,8))),IF({probability}<75,IF({mitigationCost}<5000,9),IF({mitigationCost}<10000,10,IF({mitigationCost}<15000,11,12))),IF({mitigationCost}<15000,7,8))),IF({mitigationCost}<5000,13),IF({mitigationCost}<10000,14,IF({mitigationCost}<15000,15,16))))

Regards,

Doug

[-Ray]

Thanks @lgaertner and @Doug_Den_Hoed__AtAppStore , both of you pointed me in the right direction, and I have gotten the column to populate. 

To answer your question @lgaertner I used the quotations after following the example for multiple if statement in a calculated column. https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/calculated-custom-data/if-... 

Thanks for the help!

-Ray