5 Replies Latest reply on Sep 19, 2017 7:35 AM by berndh

    TROUBLE IN IFF/AND STATEMENT

    rajesdeo

      Can Someone help me in converting below logical statements to IR.

       

      1st statement

      =IF(AND(A<=101.5,A>=98.5),(K*S),IF(A<98.5,(98.5-A)+K*S,IF(A>101.5,(A-101.5)+K*S)))

       

      2nd Statement

      IF X=30 Then A=2 ,IF X=10 Then A=2.4 Otherwise Display "Not Applicable"

       

       

      Thanks & Regards

      Rajesh Deo

        • Re: TROUBLE IN IFF/AND STATEMENT
          rhonsberg

          Hello Rajesh,

          the IIF is a very special "command" since it actually not a function like the normal IF. The problem is that both the expression on the true and false side are evaluated, and that you can't set a value there.

          Here you can find more information about the IIF Function:

          https://msdn.microsoft.com/en-us/library/27ydhh0d(v=vs.90).aspx

          There are also thousands of discussion threads in the internet.

          To solve the problem one solution is to write your own code like this:

           

          Function checkValue(ByVal x As Integer) As Object 

              If x = 30 Then

                 checkValue = 2

              ElseIf x = 10 Then

                checkValue = 2.4

              Else

                checkValue = "Not applicable"

              End If

            Exit Function

          End Function

           

          You can then use this function in an RDL expression:

          =Code.checkValue($value$)

           

          You need to replace $value$ with the expression / numeric field like Compound_Amount etc....

          You need to manually add this code snippet to the RDL file (at the bottom):

          The new OpenLAB Data Analyisis allows to add code directly into the report templates properties.

            • Re: TROUBLE IN IFF/AND STATEMENT
              rhonsberg

              Here another idea from our R&D engineer for #2:

               

              A : =Iif(x=30, 2, Iif(x=10, 2.4, "Not applicable"))

               

              the A: is not part of the expression but indcaates that you can save the value of teh expression in a variable in IR,

              The important part is: You cannot set a variable in the true / false part if the IIF expression, you just return the value to save it later- Hope this helps.

            • Re: TROUBLE IN IFF/AND STATEMENT
              ir_nerd

              Hi Rajesh,

               

              Could you please set the flag the question as answered if it worked for you?

               

              Kind Regards,

              Pierre

              • Re: TROUBLE IN IFF/AND STATEMENT
                ryoboyle

                I updated the tags on this thread to make it easier to search.

                • Re: TROUBLE IN IFF/AND STATEMENT
                  berndh

                  Hello Rajesh,

                   

                  we calculate the AV for evaluating content uniformity as follows:

                  Preparations:

                  k-value is selected via a report parameter

                  X value and s value are calculated using actual analytical data, of course (statistical evaluation of the respective aggregators

                   

                  First step:

                  Calculation of M-Value:

                  M: IIF(ROUND(avg(X),1)>101.5,101.5,"") & IIF(ROUND(avg(X),1)<98.5,98.5,"") & IIF(Round(avg(X),1)>=98.5 AND ROUND(avg(X),1)<=101.5,round(avg(X),1),"")

                   

                  Second step:

                  Using the calculated M Value, the AV-Value is calculated

                   

                  AV: =ABS(M-round(avg(X),1))+k*s

                   

                  rounding to the appropriate digits is important in this case.

                   

                  Best regards,

                  Bernd.

                  1 person found this helpful