9 Replies Latest reply on Dec 9, 2016 5:36 AM by erolst

    Calculation question.


      The "Amount in Stock" for a product is negative and this effects out the total value of a product. For example if it is 2 products with a price 5€ , total value will be 10 but if it will be -2 it will be -10. The question is : How to make the field "total value" to show 0 when the amount is 0 or negative?

        • 1. Re: Calculation question.
          David Moyer


          it sounds like an unsound practice to me, but to answer your question ...

          = Max ( 0; MyNumber )

          • 2. Re: Calculation question.

            Thank you.
            I'll try it and let you know if it works.

            • 3. Re: Calculation question.

              It do not work in that way.




              I think I need to use "If" but don't know how to write in it in a right way.

              • 4. Re: Calculation question.

                Hi there. You might want to consider why an amount could ever go negative. Rather than masking the negative value, I would let it show what the data thinks is right. Perhaps stock was added incorrectly and you actually have product in greater quantity to sell than you thought. Perhaps a vendor provided a few free samples. Perhaps the person in charge of counting inventory made a mistake.


                Depending on how you've set up your solution, there is another way to look at this. When placing an order, any amount can be entered, but that's not a reflection of stock, it's just what the customer wants, and other than a return, that number would always be positive. Stock counts could go negative, but that's helpful to know what might be backordered to a customer. Again, Amount in Stock could be negative, but an amount for a sale wouldn't be.



                • 5. Re: Calculation question.
                  David Moyer

                  I would need to see your calculation.  Let me rephrase my version ...

                  = Max ( 0; TotalValue )


                  Max ( 0; -10 ) = 0

                  Max ( 0; 10 ) = 10


                  (examples edited to match original post)

                  • 6. Re: Calculation question.

                    Or you could try this calc:


                    Let (


                    stock = -2 ;

                    price = 5 ;

                    stockVal = stock * price ;

                    result = If ( stockVal ≤ 0 ; 0 ; stockVal )

                    ] ;



                    • 7. Re: Calculation question.

                      I did it in another way.
                      A wrote a formula: If ( Amount in Stock < 0 ;profit  =  0 ; Amount in Stock * Sales Price ) , and everything is working. But it's now a new problem.... When it is amount with numbers 1,3,5,7 and so on, it show me 1 ( I don't undertand why) and when I use in amount numbers like 2,4,6,8 it shows 0 like in should. 
                      Do you may be know what is the problem ?




                      • 8. Re: Calculation question.

                        I assume this formula is in the Profit field. Try rewriting your formula without "profit =", which is not needed and may be delivering up an odd result:

                        If ( Amount in Stock < 0 ; 0 ; Amount in Stock * Sales Price )

                        1 of 1 people found this helpful
                        • 9. Re: Calculation question.

                          Now it works.
                          Thank you very much.

                          • 10. Re: Calculation question.

                            keywords wrote:

                            [...] Try rewriting your formula without "profit =", which is not needed and may be delivering up an odd result: [...]

                            ... or an even one - either 0 or 1, since it will be interpreted as a predicate and returns a Boolean result.


                            But I'd use


                            Max ( 0 ; Amount in Stock * Sales Price )


                            because if Amount in Stock <= 0, so will be the result of the multiplication (unless a product could have a negative price ...), in which case 0 is the greater of the two values.

                            2 of 2 people found this helpful