3 Replies Latest reply on Feb 20, 2018 7:09 AM by Usman Ali

    IF Statement Contains Errors

    ben.kubik

      All,

       

      Trying to write a relatively simple calculated field, but can't find the syntax issue that is driving the error message. Below is the IF statement I am trying to write. Any thoughts? Thanks

       

      IF (SUM([Quantity on Hand])/SUM([Quantity Invoiced].[Qty Invoiced in 2017]) <= .25)

      THEN '0'

      ELSEIF  (SUM([Quantity on Hand])-((SUM([Quantity Invoiced].[Qty Invoiced in 2017]))*.25))*([Unit Cost])

      END

        • 1. Re: IF Statement Contains Errors
          jason.sanford

          What is your error message?

           

          It appears you only need ELSE and not ELSEIF??

          • 2. Re: IF Statement Contains Errors
            Jim Dehner

            Ben what was the error

             

            looking at your statement it looks like the first clause is trying to result in a string when true

            and the second is trying to give you a numeric result - that won't work

            either they have to be bot numeric or both strings

             

            also you seem to have a mix of aggregate and non-aggregate -

             

            ELSEIF  (SUM([Quantity on Hand])-((SUM([Quantity Invoiced].[Qty Invoiced in 2017]))*.25))*([Unit Cost])   the unit cost here neds to be aggregated - sum(), avg(), min() etc whatever is  correct

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: IF Statement Contains Errors
              Usman Ali

              Hi Jason,

               

              In your formula Below

               

              IF (SUM([Quantity on Hand])/SUM([Quantity Invoiced].[Qty Invoiced in 2017]) <= .25)

              THEN '0'

              ELSEIF  (SUM([Quantity on Hand])-((SUM([Quantity Invoiced].[Qty Invoiced in 2017]))*.25))*([Unit Cost])

              END

               

              IF Statement is returning STRING VALUE 'O', Secondly Your ELSEIF value is not returning any thing, ELSEIF Statement should return STRING VALUE because IF Statement is Returning STRING.

               

              Check if following formula works ?

               

              IF (SUM([Quantity on Hand])/SUM([Quantity Invoiced].[Qty Invoiced in 2017])) <= .25

               

              THEN 0

               

              ELSE  ((SUM([Quantity on Hand])-(SUM([Quantity Invoiced].[Qty Invoiced in 2017])))*.25)*([Unit Cost])

               

              END

               

              Unit Cost must be aggregated according to your erquirement. It may be MIN, MAX, SUM etc.

               

              Please Let me know if this works

               

              Cheers,

              Raja Usman Ali