If then Else condition in BEx Query

[e_c]

Experts, I need help

I have two key figures (MTD Rank and YTD Rank) I'm trying to define the below condition in BEx but somehow it is not giving me correct values, My condition is defined as below.

(MTD Rank <> 0 OR MTD Rank < YTD Rank) * 02 + (MTD Rank <> 0 OR MTD Rank >= YTD Rank )* 01 + 00

But it is giving me all values as 1 instead of different values as defined as 01,02 and 03 based on condition.

I defined MTD Rank <> 0 since if the record doesn't have any value.

I Even tried with below syntax as well but still it is not giving accurate values.

MTD Rank < YTD Rank * 02 + MTD Rank = YTD Rank * 01 + 00

Need your help in resolving this issue.

[e_c]

Thank You for clarification.

[anshu_lilhori]

That is the issue..Those functions are only for display purpose and it takes the default value which it originally has for further calculations and not the one which is displayed.

Regards,

AL

[e_c]

MTD and YTD ranks are being populated by selecting the rank function under the calculations tab

[anshu_lilhori]

Hi,

Simply try this and check the results. Also make sure that you get the brackets in the same fashion.

( MTD Rank < YTD Rank)*2 + (MTD Rank >= YTD Rank )*1

No extra brackets should be there.

How is MTD rank and YTD rank calculated.. is this creating an issue. Just also add 0 in MTD rank and YTD rank formulas.. So that you get 0 instead of blank value.

I have tested on my system and the same condition works. there is nothing wrong with the syntax,it can be the issue with the underlying definition of formula.

Please check that.

Regards,

AL

[e_c]

Hi Anshu,

I've attached the output and the formulas used for this.

scenario1.pngscenario1-output.pngscenario2.pngscenario2-output.png

[anshu_lilhori]

Hello,

Can you show this with the help of proper data and the desired output so that i can visualize this in a better way.

Regards,

AL

[e_c]

Hi Anshu,

Even after splitting the condition, no luck. Any more options to achieve this requirement?.

[e_c]

Will try and let you know, Thanks for the info.

[anshu_lilhori]

Hello,

Try to split the if else condition in two different formulaga and check if it gives desired result or not.

If it gives then make a new formula again and add the two conditions inside it.

Hope this works.

Regards,

AL

[e_c]

Hi Anshu,

After applying the given logic the output is giving 2 for all the records, earlier it was giving 1 for all the records. I've attached the screenshot for your reference.The first 2 is correct as per the record ranks, but the secod should be 1 instead of 2.Appreciated if you could help me on this

scn.png

[anshu_lilhori]

Hi,

Please try this:

So the first check is that the MTD rank should not be equal to 0 and then if its less than ytd rank then 02 else 01.

(MTD Rank <> 0)*( MTD Rank < YTD Rank) * 02 + (MTD Rank <> 0) *(MTD Rank >= YTD Rank )* 01