Adding numbers doesn't work for 100% - why? weird Problem

Hello community,

I hope you can help me.
I have a simple problem I think, but too difficult for me :slight_smile:

Here my Formula that not works

(TRIMMED_MEAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Skonto Fälligkeitsdatum berechnen’] TO LAST_OCCURRENCE[‘Vorgang beendet’],
REMAP_TIMESTAMPS(“T_CEL_AT_ACCOUNTS_PAYABLE”.“CREATED_AT”, DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)), 5, 5))
+
(TRIMMED_MEAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Skonto Fälligkeitsdatum berechnen’] TO LAST_OCCURRENCE[‘Ausgleichsbeleg_geänd.’],
REMAP_TIMESTAMPS(“T_CEL_AT_ACCOUNTS_PAYABLE”.“CREATED_AT”, DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)), 5, 5))
+
(TRIMMED_MEAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Skonto Fälligkeitsdatum berechnen’] TO LAST_OCCURRENCE[‘Vorgang auf Status “Zurückgeschickt” gesetzt.’],
REMAP_TIMESTAMPS(“T_CEL_AT_ACCOUNTS_PAYABLE”.“CREATED_AT”, DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)), 5, 5))

if I speak e.g. with numbers, it is like…

7,6
+
8,5
+
-
= - (dont work)

another examble

7,6
+
8,5
+
5
= 21,1 (work)

My Problem is the - (it is like #NV or no result). This case can happen and is not wrong.

The formula only works as long as each of the 3 numbers occurs. But if one of the 3 numbers is missing and the partial result is -, the formula does not work.

I need a solution to convert my - to a 0 for the addition formula to work

Hope you can help me

Greedings from Steven

Found it by myself. The Keyword was COALESCE :slight_smile:

COALESCE(TRIMMED_MEAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Skonto Fälligkeitsdatum berechnen’] TO LAST_OCCURRENCE[‘Vorgang beendet’],
REMAP_TIMESTAMPS(“T_CEL_AT_ACCOUNTS_PAYABLE”.“CREATED_AT”, DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)), 5, 5), 0.0)
+
COALESCE(TRIMMED_MEAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Skonto Fälligkeitsdatum berechnen’] TO LAST_OCCURRENCE[‘Ausgleichsbeleg_geänd.’],
REMAP_TIMESTAMPS(“T_CEL_AT_ACCOUNTS_PAYABLE”.“CREATED_AT”, DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)), 5, 5), 0.0)
+
COALESCE(TRIMMED_MEAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Skonto Fälligkeitsdatum berechnen’] TO LAST_OCCURRENCE[‘Vorgang auf Status “Zurückgeschickt” gesetzt.’],
REMAP_TIMESTAMPS(“T_CEL_AT_ACCOUNTS_PAYABLE”.“CREATED_AT”, DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)), 5, 5), 0.0)

Thanks anyway and Merry Christmas :love_you_gesture:

1 Like