IF variable XY='header' THEN SUM-Function WHEN variable XY='item' THEN Different-SUMFUNCTION

Dear Celonis community,

we have built the following OLAP Table for our “Automatic Order Entry” (AOE) types.


We have two KPIs for that: AOE Header and AOE Item calculating either the ratio of the Sales Orders (header) or Sales Order Items (item). We would like to switch between the KPIs via the yellow highlighted dropdown.
Clicking on the “header” item in the dropdown will fill a variable with the value ‘header’; same for item.

My Formular for the column EDI % is:

CASE WHEN <%= dropdown_aoe_header_item %> = ‘header’ THEN
(SUM(CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END)
/
COUNT(VBAK.MANDT))
WHEN <%= dropdown_aoe_header_item %> = ‘item’ THEN
(SUM(CASE WHEN “VBAP”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END)
/
COUNT(VBAP.MANDT))
ELSE NULL
END

I wanted to check the value of the KPI based on the variable value. However if i click on header/item it does not make a difference, but only the first calculation is done in both cases:

SUM(CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END)
/
COUNT(VBAK.MANDT)

Does anyone have an idea if this can be done like this?

Cheers,
Marcel

Hi Marcel,

I believe an answer to another question I posed recently (Nested variables) could help.

Does something like this work (instead of the case when)?

<% if(var != "don't filter") { %>
FILTER "MY_TABLE"."MY_COL" > 1;
<%  } %>

Hey @joosbuijs,

thank you for your promt response. I belive I need something like this, but remember I do not use it as a component filter, but as a “custom KPI”.

However I tried

<% if(dropdown_aoe_header_item = “header”) { %>
SUM(CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END)
/
COUNT(VBAK.MANDT);
<% } %>

and

<% if(<%= dropdown_aoe_header_item %> = “header”) { %>
SUM(CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END)
/
COUNT(VBAK.MANDT);
<% } %>

but unfortunately neither worked. The error message is not readable :frowning:
Maybe @v.kalversberg has an idea?

Thank you guys!

Hi Marcel,

I would go for your initial formula which should work for what you want to calculate. Have you checked if the results are the same for header and item level?
What exactly do you want to count in

SUM(CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END

the header level?
You could try to specifically choose the level on which you want to aggregate like

COUNT(DISTINCT 
CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ 
THEN "VBAK"."MANDT"||"VBAK"."VBELN"
ELSE NULL END

for header and accordingly on item level something like

COUNT(DISTINCT 
CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ 
THEN "VBAP"."MANDT"||"VBAP"."VBELN"||"VBAP"."POSNR"
ELSE NULL END

Best regards,
Viana

Hi Viana,

thank you for your response. I implemented now the following code which worked for me (the hint with header or item level was good)

CASE WHEN <%= dropdown_aoe_header_item %> = ‘header’ THEN
(SUM(CASE WHEN “VBAK”.“AOE_MAPPING” =‘EDI’ THEN 1.0
ELSE 0.0 END)
/
COUNT(VBAK.MANDT))
WHEN <%= dropdown_aoe_header_item %> = ‘item’ THEN
(SUM(PU_COUNT(VBAK,VBAP.MANDT,“VBAP”.“AOE_MAPPING” =‘EDI’))
/
SUM(PU_COUNT(VBAK,VBAP.MANDT)))
ELSE NULL
END

The table should have header dimension (dimension is VBAK.VKORG -> Sales Org). I think that is why I had to have also the second formular pulled on header-level. What do you think?

Thank you for your answers.