6 Replies Latest reply: May 3, 2013 8:13 PM by Manohar Delampady RSS

IF THEN ELSE in BEX Query Designer

Dhanuka De Silva
Currently Being Moderated

Hi All,

 

In the query designer im using the following formula to calculate one of our figures and its working perfectly.

 

NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )

 

When i use this some of the values are coming more than 100%. which is perfectly fine.

 

Now there is a requirement to cap off the figures at 100% level. So i did as below.

 

( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  >= 100) *

 

( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ) == 100 ) +

 

NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )

 

 

However when i execute the report figures are not coming as i expect them to. What seems to be the issue ? Is there any step that i'm missing?

 

Regards,

 

DTD


  • Re: IF THEN ELSE in BEX Query Designer
    Manohar Delampady
    Currently Being Moderated

    Hi Dhanuka De Silva,

         I guess what you want to do is IF NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ) is greater than 100 then the output should be 100, else display the exact percentage. If my understanding is right then the only thing wrong is the usage of NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ) ==100. Because the system is considering this as another condition as there is an "==" symbol.

     

    Your Formula:

    ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  >= 100) *

     

    ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ) == 100 ) +

     

    NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )

     

    To be edited as below:

    ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  >= 100) *

     

    100 + ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  < 100) *

     

    (NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ).

     

    Hope this helps. Sorry for the FONT and color irregularities.

     

    Regards,

      Manohar.D

    • Re: IF THEN ELSE in BEX Query Designer
      Manohar Delampady
      Currently Being Moderated

      Hi Dhanuka De Silva.

           Was curious to know if the formula suggested by me worked. I've used IF-ELSE condition in key figures in the same manner before and they've worked for me.

       

      Formula suggested by me:

      ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  >= 100) * 

      100 + ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  < 100) *

      (NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ).

       

      Description: Percentage variance of ONTIME to ORDQTY if greater than or equal to 100, then display output as 100, elseif the same condition is lesser than 100 then display the percentage variance as it is.

       

      Regards,

        Manohar. D

      • Re: IF THEN ELSE in BEX Query Designer
        Dhanuka De Silva
        Currently Being Moderated

        Hi Manohar,

         

        Thanks and this is working fine. Closing thread.

         

        Regards,

        DTD

        • Re: IF THEN ELSE in BEX Query Designer
          Dhanuka De Silva
          Currently Being Moderated

          Hi Manohar,

           

          I have another question. When I use the query formula you mentioned it comes as a value not a percentage figure ( % symbol is not there ). How do I get the value as a percentage mark ?

           

          Because we use the %A symbol figures should come as percentage.

           

          Appreciate your reply on this.

           

           

          Regards,

          DTD

          • Re: IF THEN ELSE in BEX Query Designer
            Manohar Delampady
            Currently Being Moderated

            Hi Dhanuka,

                    I'm really not sure on this but still you can check in query properties if "Display scaling factors" is checked or not. Will try creating a dummy query and testing the percentage variance function. I believe that it should be related to scaling factor and when I had used such percentage functions before I don't remember finding the percentage function.

            In anycase if I dont come back with a response, kindly post this as a new question so that others could get a chance to look at this issue.

             

            Regards,

              Manohar.D

            • Re: IF THEN ELSE in BEX Query Designer
              Manohar Delampady
              Currently Being Moderated

              Hi Dhanuka De Silva,

                   I checked with a simple percentage share formula and found that the percentage symbol would be displayed unless "Display Scaling Factor" in query properties is checked. Please uncheck the option and see, if not then we might have to change the formula by adding a % symbol after the first clause like below:

               

              ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  >= 100) *

              100% + ( NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) )  < 100) *

              (NOERR ( ( NODIM ( ONTIME ) %A NODIM ( ORDQTY ) ) ).

               

              Hope this helps, if not please open another thread and we might have experts suggesting the answers. In any case do educate back here regarding what addressed the issue.

               

              Good Luck.

               

              Regards,

                 Manohar. D

Actions