Forum Discussion

BillyBussey's avatar
BillyBussey
Qrew Trainee
6 years ago

My code using Sum function with the - operator.

 What is wrong with my code?   [Apply Discount] is a checkbox  the rest are numeric formula fields.  [Discount] is a numeric field.  It will Sum but fails to subtract the [Discount] even when not used in the IF statement. 

If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]-[Discount]),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
Tables and fields

10 Replies

Sort By
  • EvanMartinez's avatar
    EvanMartinez
    Qrew Elite
    6 years ago
    Hi Billy,

    Have you tried wrapping just the section with the subtraction in its own set of parenthesis?

    If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],([Removal Total]-[Discount])),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))

    Just to make sure that Subtraction is being calculated first.
  • BillyBussey's avatar
    BillyBussey
    Qrew Trainee
    6 years ago
    It still doesn't do the subtraction.  I get the sum total whether Apply discount is checked or not. 
  • EvanMartinez's avatar
    EvanMartinez
    Qrew Elite
    6 years ago
    Could you try putting in 1 instead of true for your checkbox really quick and see what you get back?

    So [Apply Discount]=1
  • BillyBussey's avatar
    BillyBussey
    Qrew Trainee
    6 years ago
    Good idea though, Here is the code that worked.    What you said made me think.  

    If([Apply Discount]= true, Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])-([Discount]),Sum([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total]))
  • BillyBussey's avatar
    BillyBussey
    Qrew Trainee
    6 years ago
    The code I posted above worked.  Just the subtraction part needed to be in its own set of parentheses.  
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      6 years ago
      The parens around the subtraction value aren't relevant, it's that you needed to do the subtraction outside the arguments for Sum().

      In other words your final code
      Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])-([Discount])
      works, and this would also work
      Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total])-[Discount]

      I don't know offhand if
      Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total],-[Discount])
      would work, but I suspect not

      Addendum - if for some reason you HAD to do it that way (no idea why you might, just go with it), you'd either put negative values in [Discount] or else create a field (called [Discount to Apply] or something) with a formula [Discount]*(-1) and use that in your sum formula:
      Sum ([SoftScape Total],[HardScape Total],[Aggregate Total],[Removal Total],[Discount to Apply])