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
)
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 !
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
)
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")
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.
