Column chart KPI: count of cases that match specific condition

Trying to create a KPI for my column chart, where I would count only those cases for which a specific condition is met.

In particular, I want to count the number of SO Items (COUNT_TABLE(“VBAP”)), which were created via EDI (“VBAK”.“BSARK” = ‘DFUE’).

Tried this statement, but getting an error:
CASE WHEN “VBAK”.“BSARK” = ‘DFUE’ THEN COUNT_TABLE(“VBAP”) ELSE NULL END

Error:

Dimension is ROUND_MONTH(“VBAK”.“ERDAT”) if that makes the difference.

UPDATE: in addition, important that in the end I want to show on the same column chart also the number of SO Items that were created not with EDI. I.e. I want to have a stacked column that consists of all SO Items and I see the part with EDI and the part without EDI.

Help appreciated!

Regards
Masha

Hi Masha,

the count_table() is sort of a convenience function to simply count records in a table. It does not work with conditions.
Also, aggegations such as count, avg, sum,… should always happen on the highest level of a formula, not inside other functions because it could lead to unexpected behavior, or errors like yours.

For your case, I’d recommend doing something like this:

COUNT(DISTINCT CASE WHEN "VBAK"."BSARK" = 'DFUE' THEN "VBAP"."VEBLN"||"VBAP"."POSNR" ELSE NULL END)

This counts the distinct number of SO Items (Thus VBELN || POSNR) where the condition on the SO Header is met.

To count the rest of the SO Items, simply do the same but with “VBAK”.“BSART” != ‘DFUE’.

If you would want to count only SO Headers, just use “VBAP”.“VEBLN” instead of “VBAP”.“VEBLN”||“VBAP”.“POSNR”

Hope that helps
Best
Max

And a huge thanks again! Your solution worked, and I also was able to create a chart with net order value with the same logic :clap:t2: :clap:t2: :clap:t2:

Sorry for asking maybe quite beginners questions, I don’t have that much experience with Celonis yet. But I am happy that I can learn from experienced colleagues via this forum.

1 Like

You’re welcome! :+1: