My code using Sum function with the - operator.

  • 40
  • 1
  • Question
  • Updated 3 months ago
  • Answered
 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]))
Photo of Billy Bussey

Billy Bussey

  • 150 Points 100 badge 2x thumb
  • frustrated

Posted 3 months ago

  • 40
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,264 Points 5k badge 2x thumb
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.
Photo of Billy Bussey

Billy Bussey

  • 150 Points 100 badge 2x thumb
Let me give it a shot. 

Photo of Billy Bussey

Billy Bussey

  • 150 Points 100 badge 2x thumb
It still doesn't do the subtraction.  I get the sum total whether Apply discount is checked or not. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Billy,
Is the discount a dollar amount number or a %?
Photo of Billy Bussey

Billy Bussey

  • 150 Points 100 badge 2x thumb
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]))
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,214 Points 5k badge 2x thumb
Nice I'm glad to hear that worked
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,264 Points 5k badge 2x thumb
Could you try putting in 1 instead of true for your checkbox really quick and see what you get back?

So [Apply Discount]=1
Photo of Billy Bussey

Billy Bussey

  • 150 Points 100 badge 2x thumb
It is a dollar amount. 
Photo of Billy Bussey

Billy Bussey

  • 150 Points 100 badge 2x thumb
The code I posted above worked.  Just the subtraction part needed to be in its own set of parentheses.  
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])
(Edited)