Discussions

Expand all | Collapse all

Need to include ""Later Of"" to a set normally calculates ""Earlier Of

  • 1.  Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 19:07
    I have a set of formulas that calculate a date based on a list of terms in a field. Until now, the terms that had two parts were "the earlier of"  As such, I compared the two results using the "Min" function in another formula. Now I have two new terms that are based on the "the later of" two dates, so the "Min" function won't work anymore.

    Looking for suggestions.


    Example of the comparison formula:
    Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])



  • 2.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 19:10
    Max() is the function you need.


  • 3.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 19:17
    Problem is that the list of terms now includes both Min and Max


  • 4.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 19:27
    Can you write out in English the comparison you're trying to write? If Forrest's answer isn't the answer, I don't understand the question.


  • 5.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 19:39
    So, I have over 60 different terms listed in a field. These are the examples of the 3 types of terms (single, earlier of, later of)

    180 days from end of FW
    earlier of 6 mos end of FW / 9 mos from beginning of FW
    later of 6 mos end of FW / 24 mos from 1st pymt

    I have a case statement formula calculating the single & 1st date ([MSO Delivery Deadline1]), and another one to calculate the 2nd date ([MSO Delivery Deadline2]).

    Then I have a 3rd formula to compare the 2, returning the earlier of the dates.

    Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])

    My problem is that they just gave me new terms that are "the later of" now. Not sure how to calculate both in the same formula.





  • 6.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 20:11
    I'm sorry, I don't understand what you are trying to do.

    Everything up to 'My problem is' in your comment is fine. But I don't see where you describe the comparison you're trying to write. Again, you have the Min() correctly to calculate the second date, and to calculate the third date you need a Max() for that; it seems that you understand that.

    What do you mean by 'calculate both in the same formula'? Can you write out in English the entire comparison you need to do? You calculate the first date, calculate the second date, then you compare the two, then... what?


  • 7.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 20:43
    Currently, it calculates like this...
    *1st Formula Field: If the term is X, calculate the single term or the 1st half date for [MSO Delivery Deadline1]
    2nd Formula Field: If the term is X (only if it contains an "earlier of" term, calculate the 2nd half date for [MSO Delivery Deadline2]
    3rd Formula Field: Compare [MSO Delivery Deadline1] & [MSO Delivery Deadline2], and return the earlier date.

    I was just given new terms that want me to calculate the "later of" date, in the same set of formulas. Currently, my comparison only allows for the "earlier of" result, and I'm not sure how to have those new terms calculate on the "later of" result.

    *My 1st formula field is over 60 lines long, or I'd paste it in here.


  • 8.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 20:48
    I was just given new terms that want me to calculate the "later of" date, in the same set of formulas."

    Can you tell us anything about this? Later of what? Compared to what? How does this relate to the inputs and/or outputs of the first three formulas? What is the desired outcome?


  • 9.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-10-2019 21:31
    The first term has always been part of the current formulas for the "earlier of," but the second one is a new term.
    "The earlier of 2 years from 1st payment  / 10 months from end of fieldwork"

    "The later of 6 months from end of fieldwork / 24 months from 1st payment"

    This is an abbreviated version of the 1st case statement formula field:
    MSO Delivery Deadline1
    Case ([Report Delivery Window - MSO (MSO-NET)] , "30 Days from when all relevant documents are received" , [Fieldwork End Date (AUD)] + Days(30) ,
    "30 Days from end of FW" , [Fieldwork End Date (AUD)] + Days(30) ,
    "60 days from end of fieldwork" , [Fieldwork End Date (AUD)] + Days(60) ,
    "3 mo from leaving MSO office" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
    "3 mo from leaving MSO office / 24 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
    "3 mo from leaving MSO office / 27 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
    "3 mo from leaving MSO office / 30 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) )

    The second case statement formula field, calculated the other half of the "or" terms:
    MSO Delivery Deadline2
    Case ([Report Delivery Window - MSO (MSO-NET)] , "3 mo from leaving MSO office / 24 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(730) ,
    "3 mo from leaving MSO office / 27 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(840) ,
    "3 mo from leaving MSO office / 30 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(930) )

    The third formula field is the one that returns the "earlier of" date:
    MSO Delivery Deadline Calc1
    Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])

    These 3 fields are used to return the date for the final deadline.






  • 10.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-11-2019 01:25
    I think I'm getting closer. Are you saying that, right now, you have three fields as you've described, to answer the question:
    "The earlier of 2 years from 1st payment  / 10 months from end of fieldwork"
    With a single output?

    And now you need to start from scratch to answer the question:
    "The later of 6 months from end of fieldwork / 24 months from 1st payment"
    With a separate output, based on a separate calculation, independent of everything you've shown so far?

    IS there a relationship between 
    "The earlier of 2 years from 1st payment  / 10 months from end of fieldwork"
    and
    "The later of 6 months from end of fieldwork / 24 months from 1st payment"
    ?


  • 11.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-14-2019 18:18
    I found a solution to part of the problem. I created a checkbox formula that shows true when the term contains "Later" in the field. And then changed the first calculation to the following:

    If ([MSO-NET - MSO Window Later]=true, Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]), Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))

    Now I need to figure out how to apply the same logic to the second calculation:

    If (not IsNull([Fieldwork Ends (AUR Specific)]) , Min ( [MSO Delivery Deadline3] , [MSO Delivery Deadline4] ))




  • 12.  RE: Need to include ""Later Of"" to a set normally calculates ""Earlier Of

    Posted 01-14-2019 18:43
    Final solution, adding another calculation field to handle the Min/Max question, and then returning that result if the "not IsNull" was true.


    MSO Delivery Deadline Calc1 Formula
    If ([MSO-NET - MSO Window Later]=true,
    Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]),
    Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))

    MSO Delivery Deadline Calc1-2 Formula
    If ([MSO-NET - MSO Window Later]=true,
    Max([MSO Delivery Deadline3], [MSO Delivery Deadline4]),
    Min ([MSO Delivery Deadline3], [MSO Delivery Deadline4]))

    MSO Delivery Deadline Calc2 Formula
    If (not IsNull([Fieldwork Ends (AUR Specific)]) , [MSO Delivery Deadline Calc1-2])

    MSO Report Deadline Formula
    Nz([MSO Delivery Deadline Calc2], [MSO Delivery Deadline Calc1])