Analysis for Office IF THEN ELSE

[dedel]

Hi,

I've spent the better part of an afternoon browsing Google and SAP Help topics to find an answer.

My request is simple, I would like to add simple IF THEN ELSE logic to an 'Advanced Calculation' in SAP Analysis for Office. I know Boolean operators are available, though I can't seem to find (or they're non-existent) comparison operators. I've tried the BEx formula's/logic using Boolean operators but that doesn't seem to work.

The following source: (https://www.reyemsaibot.com/2016/05/09/analysis-office-data-analysis-advanced-calculations/)

seems to describe the IF THEN ELSE logic in Analysis for Office, but I can't get it to work.

For demo purposes I'm trying to create another column which takes it's value from the following pseudo code: IF 'Number of Records' > 0 THEN 1 ELSE 0.

Please find below a couple of screenshots to make clear what I'm looking at: advancedcalculation.png(82.8 kB)

[KonradZaleski · Accepted solution]

Hi Diedrick,

Which version of AFO are you using? In my AFO version 2.6 I have multiple boolean operators:

Then when I apply following condition:

I'm getting desired output ('1' if the condition is met, otherwise '0')

Consider that it is not IF ELSE function, but it just gives boolean value.

Optionally you can add new column after your Number of Records column:

And then apply excel IF formula:

Also this new column will be dynamically adjusting the result depending on filters and context.

[dedel · Accepted solution]

Thanks, this wasn't clear from the user guide! I'm using version 2.5.x so I don't see the comparison Boolean operators.

[RafkeMagic]

euhm, by using the AFO options to "add" a column, you're not pushing your logic to BW either

if you want to push logic to BW, you should change the query definition

[RafkeMagic]

Why don't you just add a new column and use an Excel function for that? Works perfectly fine here.

[dedel]

Hi, hanks for your answer! I agree with you this works for simple logic or small data-sets. However I would like to have to possibility to push as much logic as possible back into BW and keep heavy calculations out of Excel.