How to write "IF THEN ELSE" statement with BEX query Key Figure technical ID object in SAC

[tek_ib]

Hello,

I am trying to create a calculation in SAP analytics cloud on story level which is 

• IF Net Invoice Value is negative then “Credit memo”

My source is BEx query and I have a key figure in the BEx query i.e "Net Invoice Value" which I want to use in the calculated measure.

The technical id of Net Invoice Value in the BEX query is [00O2TKK8G8XRQO3VD5VBZC115]

I tried the following syntax while creating the calculated measure on a story level i.e

IF([00O2TKK8G8XRQO3VD5VBZC115] < 0, "Credit Memo", "")

but I am getting an error message below while writing this logic.

"Formula could not be computed: Object is unknown: 00O2TKK8G8XRQO3VD5VBZC115. Please complete or rewrite the formula".

I will appreciate if you let me know what is wrong with the formula and how to use BEx object in the SAC calculation which has technical ID associated with it.

Thank You very much in advance.

Regards

[nachtaktiv]

why do you use the technical ID of this measure instead of the name in the BW query ?

[Yogeshwar_M]

Hi,

SAC formulas do not support direct use of technical IDs like [00O2TKK8G8XRQO3VD5VBZC115].

Use the measure name as it appears in your SAC story or the data source browser.

For example 

IF([Net Invoice Value] < 0, "Credit Memo", "").

Thanks 

[tek_ib]

Hi @Yogeshwar_M I have already tried the formula expression you suggested and it is giving me an error. Do I need to use the datasource name before the field name Net Invoice Value to complete the formula or Is there anything I need to put after the field name such as Net Invoice Value.something ?

[eeddggaarr]

Hi tek_ib,

you have to use numeric values only for if - then conditions in SAC and "Credit memo" is a string so this will not work.
with an import connection you could simply create a calculated dimension. For BW live this is not yet available - please vote here. However, your BW team could introduce a new navigation characteristic which is based on your keyfigure. then you could define ranges in BW and which text should be shown for them.

if-then-else in general:
if you write 'if' (without ' ') into calculation editor SAC's auto-completion will create the needed structure:

Now you simply start with the first two numbers of measure ID '00' and SAC will present to you all available measures from your query:

you could also use the description of your measure:

this methods will prevent typos.
BR
Edgar

[tek_ib]

Hello Edgar, Thanks for the clarification. You are partially correct but I have to use both KF and string in one syntax. For example, {If Net invoice value < 0 Then Invoice status should be "credit memo"] Invoice status is another dimension and it has values such as "credit memo" "Pending payment" etc.