Discussions

 View Only
  • 1.  My code using Sum function with the - operator.

    Posted 09-24-2018 13:20
     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]))


  • 2.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:24
    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.


  • 3.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:27
    Let me give it a shot. 



  • 4.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:29
    It still doesn't do the subtraction.  I get the sum total whether Apply discount is checked or not. 


  • 5.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:30
    Billy,
    Is the discount a dollar amount number or a %?


  • 6.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:32
    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]))


  • 7.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:33
    Nice I'm glad to hear that worked


  • 8.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:32
    It is a dollar amount. 


  • 9.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:32
    Could you try putting in 1 instead of true for your checkbox really quick and see what you get back?

    So [Apply Discount]=1


  • 10.  RE: My code using Sum function with the - operator.

    Posted 09-24-2018 13:33
    The code I posted above worked.  Just the subtraction part needed to be in its own set of parentheses.  


  • 11.  RE: My code using Sum function with the - operator.

    Posted 09-25-2018 20:02
    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])