Columns - Nested If Statement | Community
Skip to main content
R
RayCOHenry
August 8, 2023
Solved

Columns - Nested If Statement

  • August 8, 2023
  • 3 replies
  • 1106 views

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",""))

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Doug_Den_Hoed__AtAppStore

 

Hi @raycohenry,

 

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

3 replies

lgaertner
lgaertner
August 8, 2023

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
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity AdvisorAccepted solution
    Community Advisor
    August 8, 2023

     

    Hi @raycohenry,

     

    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

      R
      RayCOHenryAuthor
      August 9, 2023

      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-statements-overview.html?lang=en#multiple-if-statements 

       

      Thanks for the help!

       

      -Ray

        Terms & ConditionsAccessibility statement

        Loading footer...