Hi, maybe someone can help me with my issue.

Hi,

Try this approach:

SUM(

case when "AFVV"."VGE01" = 'MIN'

Then ("AFRU"."GMNGA")*"AFVV"."VGW03"/"PLPO"."BMSCH" / 1

ELSE

Case when "AFVV"."VGE01" = 'S'

THEN ("AFRU"."GMNGA")*"AFVV"."VGW03"/"PLPO"."BMSCH" / 60

ELSE

CASE WHEN "AFVV"."VGE01" = 'Day'

THEN ("AFRU"."GMNGA")*"AFVV"."VGW03"/"PLPO"."BMSCH") / 1*60*60

ELSE 0

END

)

Great. It is working. Thanks !

As mentioned the formula is working.

Based on the number of confirmations per activity, I have to divide the sum by the number of confirmations.

This is fine when having a sheet with some more distinguishing elements.

When I want to sum up this standard working up on plant level using a chart, the formula is calculating wrong by first summing up all working time and then divide it by the sum of all confirmations instead of summing this value up by each line.

Any advise for this issue?

I'm not sure I understand the problem completely, but I would try replacing COUNT_TABLE with PU_COUNT("AFVV", "AFRU.RMZHL")

Replacing COUNT_TABLE with PU_COUNT I receive again an error message.

I try to explain my issue more precisely.

This is my whole code.

SUM(

 CASE WHEN "AFVV"."VGE01" = 'MIN'

 THEN "AFVV"."VGW01"

 ELSE

  CASE WHEN "AFVV"."VGE01" = 'S'

  THEN "AFVV"."VGW01" / 60

  ELSE

   CASE WHEN "AFVV"."VGE01" = 'Day'

   THEN "AFVV"."VGW01" * 60 * 60

    ELSE 0

   END

  END

 END)

/ COUNT_TABLE("AFRU"."RMZHL"))

*

(SUM(CASE WHEN "AFVC"."SPANZ" = 0

THEN 1

ELSE "AFVC"."SPANZ"

END)

/ COUNT_TABLE("AFRU"."RMZHL"))

The first part of this code is taking the standard working time from routing and divides it by the no. of confirmations per activity.

The second part of the code is multiplying the first result with the result of the number of splits divided by the number of confirmationes per activity.

Having the order below as an example:

When calculating this on activity level in a spreadsheet the formula is correct.

Celonis

Calculation in detail in Excel

Now using the same formula, I am trying the sum up all working time on plant level for all acitivities in a graph, but the result is wrong.

Celonis 

Celonis is calculating as follow:

Summing up all working times of all activities first and then divide this by all confirmations. So there is no 1:1 relation and calculation based on the actitivity and the order. Same for the second part.