Dear Celonis community,
we have built the following OLAP Table for our Automatic Order Entry (AOE) types.
image1101180 5.54 KB
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
Page 1 / 1
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)?
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
Maybe @v.kalversberg has an idea?
Thank you guys!
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
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
the header level?
You could try to specifically choose the level on which you want to aggregate like
for header and accordingly on item level something like
Best regards,
Viana
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.
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.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.