Discussions

Expand all | Collapse all

Multiple Criteria for 1 Line in a Case Statement

  • 1.  Multiple Criteria for 1 Line in a Case Statement

    Posted 12-06-2019 13:28
    I have a series of Case statements that are used to calculate a due date, based on a large number of contract terms (ex. 6 mos end of FW / 24 mos 1st pymt, 6 mos end of FW / 30 mos from 1st pymt). There are two large case statement formulas to calculate the two side of the terms, and then another formula that returns the earlier of the two dates.

    My problem is that sometimes those terms are based on another value that will return what we refer to as a Null Date (ex. 01-01-0001), so I want to be able to put a qualifier to the individual lines in the case statement. For that term, the logic would be that if the "other term" equals Null Date, then return "1st calculation," otherwise use "2nd calculation."

    This is an abbreviated version of the Case Statement:
    Case ([Report Delivery Window - MAI] , "8 weeks from end of FW / no later than 12 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(12) ,
    "90 days from location exit / 90 days from end of FW" , [Fieldwork End Date (AUD)] + Days(90) ,
    "90 days from end of FW / 6 mos from start of FW" , AdjustMonth([Fieldwork Begin Date (AUD)] , 6) ,
    "90 days from the end of FW / 6 mos from end of FW" , [Fieldwork End Date (AUD)] + Days(180) ,
    "90 days from end of FW / 30 days before MSO Deadline" , [MSO Report Deadline] - Days(30) ,
    "4 mo from end of FW/no later than 1 year from start" , AdjustMonth([Fieldwork Begin Date (AUD)] , 12) ,
    "6 mo from beginning/no later than 6 mo end" , AdjustMonth([Fieldwork End Date (AUD)] , 6) ,
    "6 mo from beginning/no later than 3 mo end" , AdjustMonth([Fieldwork End Date (AUD)] , 3) )

    The term in question is "90 days from end of FW / 30 days before MSO Deadline." I'm not sure how to write the extra formula within the formula.

    ------------------------------
    Jennifer Peterson
    ------------------------------


  • 2.  RE: Multiple Criteria for 1 Line in a Case Statement

    Posted 12-06-2019 13:44
    You can have an IF statement within the Case

     for example

    "90 days from end of FW / 30 days before MSO Deadline" , IF(condition is true,[do this], [else do that]),


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Multiple Criteria for 1 Line in a Case Statement

    Posted 12-06-2019 14:01
    Awesome, I wasn't sure I could stack them like that. Thank you

    ------------------------------
    Jennifer Peterson
    ------------------------------



  • 4.  RE: Multiple Criteria for 1 Line in a Case Statement

    Posted 01-06-2020 14:02
    Edited by Thomas LaMotte 01-06-2020 14:03
    Is the same thing true with Case statements within Case statements? Or Case statements within If statements?

    ------------------------------
    Thomas LaMotte
    ------------------------------



  • 5.  RE: Multiple Criteria for 1 Line in a Case Statement

    Posted 01-06-2020 14:23
    Yes

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------