Skip to main content
Question

Column chart KPI: count of cases that match specific condition

  • September 8, 2020
  • 3 replies
  • 37 views
M
Level 5
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

3 replies

maximilian.hoffm13
Level 4
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: 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
M
Level 5
  • masha.galit
  • Author
  • Level 5
  • September 9, 2020
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.
maximilian.hoffm13
Level 4
Youre welcome!
Community Code of ConductAccessibility statement