Discussions

 View Only
  • 1.  Daisy If statements

    Posted 05-22-2020 09:23
    How do I get these two if statements in to a numeric formula? I am getting errors when I insert just like below.

    If([Total Gross Billed Amount: BoM Ordered]>[BoM Ordered Amount]and [Total Gross Billed Amount: BoM Ordered]=0,0,[Gross BoM Ordered Balance]*0.5)

    If([Total Gross Billed Amount: BoM Ordered]>[BoM Ordered Amount]and [Total Gross Billed Amount: BoM Ordered]>0,0,[Gross BoM Ordered Balance]) 


    ------------------------------
    GWB
    ------------------------------


  • 2.  RE: Daisy If statements

    Posted 05-22-2020 09:35
    In general, you start with in IF and then then just list the tests in the order you want them evaluated.  The first one that is TRUE wins!
    But I am unable to understand the business logic that you are looking for.  Can you say it in words?

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



  • 3.  RE: Daisy If statements

    Posted 05-22-2020 09:44


    George,

    If(
      [Total Gross Billed Amount: BoM Ordered]=0,
      0,
    [Gross BoM Ordered Balance]*0.5)
    
    if(
      [Total Gross Billed Amount: BoM Ordered]>0,
      0,
      [Gross BoM Ordered Balance]) ​


    You've got these two statements in there which contradict one another.

    Let's examine what would happen if BoM Ordered was 1, 0, or -1.

    If BoM ordered is  1, the first statement would return '[Gross BoM Ordered Balance]*.5' and the second would return 0.

    If BoM ordered is  0, the first statement would return 0 and the second would return [Gross BoM Ordered Balance].

    If BoM ordered is -1, the first statement would return '[Gross BoM Ordered Balance]*.5' and the second would return 0.

    So these are incompatible, if you want them to return in the same statement. If you can tell me what the formula should return for BoM ordered being 1, 0, and -1, the formula can be combined.

    If you need more help with it, swing by Data Collaborative's free Office Hours, every Thursday at 1pm Eastern.

    Sign up here:
    https://data.quickbase.com/db/bqeqqj33i






    ------------------------------
    - Sam

    ______________________________________________
    Sam Jones
    Vice President, Product and Technology
    he/him

    The Data Collaborative, Inc.
    sjones@datacollaborative.com
    366 Massachusetts Ave, Suite 203 | Arlington, MA 02474
    ------------------------------



  • 4.  RE: Daisy If statements

    Posted 05-22-2020 09:52
    Basically each of the IF's in the formula alone provide correct results for some records but not all records. Somehow I have to write one formula that accounts for both situations.

    ------------------------------
    George George
    ------------------------------



  • 5.  RE: Daisy If statements

    Posted 05-22-2020 09:59
    I can help you but you first need to be able to say in plain english words what the conditions and results should be.  Never mind the code for now, what is the business logic that you want.


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