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:
image77760 4.71 KB
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
Page 1 / 1
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, Id recommend doing something like this:
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
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, Id 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 
Sorry for asking maybe quite beginners questions, I dont have that much experience with Celonis yet. But I am happy that I can learn from experienced colleagues via this forum.
Sorry for asking maybe quite beginners questions, I dont have that much experience with Celonis yet. But I am happy that I can learn from experienced colleagues via this forum.
Youre welcome! 
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.