Skip to Content
P J

SAP BW Bex query Calculated column logic requirement

Hi Friends,

I have query on composite provider. I have below data and column in yellow color, is I am currently getting in my query. But need logic to display data in column green. LTM in column green is correct output. (We are on SAP BW 7.4 HANA DB)

Logic/condition is-> If( (Year1_YTD = 0) or (Year2_YTD = 0) or (Year3_YTD = 0) then LTM = 0

Else LTM= Top_YTD - I/p_YTD)

I have created formula in my columns using IF ELSE logic using 'Logical OR Boolean operator':

( (Year1_YTD == 0) OR (Year2_YTD == 0) OR (Year3_YTD == 0)) * 0 + (Top_YTD - I/p_YTD)

But this does not work and no change in result set happen. It looks like Logical OR only works on tow expressions:

<Expression1> OR< Expression2>

Please help to get some pointer to build above logic in query designer.

Thank you!

ind2o.png (8.2 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Best Answer
    Posted on Jan 30, 2019 at 07:17 PM

    Hi All,

    Thank you for looking into this and sending your comments.

    Issue has resolved with below change

    Hidden Formula1: (Year1_YTD* Year2_YTD* Year3_YTD) (This return 0 if any of YTD is 0)

    LTM: Formula2: (Formula1 <> 0) * LTM + 0

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 24, 2019 at 10:34 AM

    Hi ,

    You need to write the else part completely as well.

    ( (Year1_YTD == 0) OR (Year2_YTD == 0) OR (Year3_YTD == 0)) * 0 + ( (Year1_YTD <> 0) AND (Year2_YTD <> 0) AND (Year3_YTD <> 0)) *(Top_YTD - I/p_YTD)

    This should work i believe.Please give it a try.

    In case this does not work then create a new formula lets say formula 1 and inside that multiply your year1_ytd,year 2 and so on.

    This way either you will get 0 or some value.

    Then write if else condition based on the above formula 1

    (formula1 == 0) * 0 + (formula1 <> 0 )*(Top_YTD - I/p_YTD)

    Regards,

    AL

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 24, 2019 at 03:55 PM

    Hi Anshu,

    Thank you for stopping by and taking time to reply on my question. I tried both ways you mentioned. First did not worked. For 2nd option, in formual1 I am getting 0 when doing multiplication but when I used it in like:

    (formula1 == 0) * 0 + (formula1 <> 0 )*(Top_YTD - I/p_YTD), I am still getting some value in LTM column though Formula1 is 0. Very strange.

    I tried below and it worked and report is giving correct data now.

    Formula1: (Year1_YTD == 0) OR (Year2_YTD == 0) OR (Year3_YTD == 0) (This return 1 if any of YTD is 0 else return 0 if all Year_YTDs are non zero)

    LTM: Formula2: (Formula1 <> 1) * LTM + 0

    Thank you again for your help.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 28, 2019 at 06:30 AM

    Hi,

    You need to it this way.

    Create a new formula.

    NEW_FORMULA = Top_YTD - I/p_YTD

    Then do the IF statement:

    (Year1_YTD == 0) * 0 +

    ( (Year2_YTD == 0) * 0 +

    ( (Year3_YTD == 0) * 0 + NEW_FORMULA) )

    Regards,

    Loed

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 30, 2019 at 10:43 AM

    Hi PJ,

    your statement

    ( (Year1_YTD == 0) OR (Year2_YTD == 0) OR (Year3_YTD == 0)) * 0

    will always deliver a 0 no matter what the result of the boolean checks deliver, because you multiply with 0.

    you need to redfine the booleans to something, that give in the correct case a 1 which is then multiplied with your desired formula result.

    regards

    Cornelia

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 30, 2019 at 11:10 AM

    Try this one:

    ( (Year1_YTD <> 0) AND (Year2_YTD <> 0) AND (Year3_YTD <> 0)) * (Top_YTD - I/p_YTD)

    Regards,

    Frederic

    Add a comment
    10|10000 characters needed characters exceeded