This question was closed Jan 30, 2019 at 07:17 PM by P J for the following reason: The question is answered, right answer was accepted
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!
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
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
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.
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
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
Try this one:
( (Year1_YTD <> 0) AND (Year2_YTD <> 0) AND (Year3_YTD <> 0)) * (Top_YTD - I/p_YTD)
Regards,
Frederic
Add a comment