IF THEN ELSE in BEX Query Designer

[Former Member]

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

[former_member209895 · Accepted solution]

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

[former_member209895 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Manohar,

Thanks and this is working fine. Closing thread.

Regards,

DTD

[Former Member · Accepted solution]

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

[former_member209895 · Accepted solution]

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

[former_member209895 · Accepted solution]

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