Function formatNumber(...) for aggregated SUM(...) with NaNs

Hi Rafal,

I guess you use the formatNumber() function in an action flow? The error that you get is caused by an empty SUM result. In other words, only NULL values are summed which result in NULL. NULL is nothing, not an empty string, not 0. This is also what the error says, 'NaN' stands for 'Not a Number'. Therefore, the formatNumber() cannot be applied.

You can solve this in multiple ways that I can think of:

1. Be sure that the sum will never be empty (add 0 to it to make the sum 0, which is actually a number)
2. Bypass this step by using rerouting when the sum is null
3. Depending on the next steps, let the flow ignore this error and just continue.

I hope this helps!

Hi @janpeter.van.d, thank you for your answer.

Yes, I use it inside an action flow.

Indeed, in some cycles probably NULL values are summed.

I tried to add 0.0 in every cycle of the flow to this sum, but it doesn't work - I still receive The operation failed with an error. Failed to map 'formula': Function 'formatNumber' finished with error! 'NaN' is not a valid number or using unsuitable separator.

The structure which causes the problem is formatNumber(SUM(...);2).

... = CASE WHEN construction

I tried with

• formatNumber(0.0+SUM(...);2)
• formatNumber(ADD(0.0,SUM(...));2)
• formatNumber(SUM(0.0,SUM(...));2)
• formatNumber(SUM(0.0+...));2)
• formatNumber(SUM(ADD(0.0,...));2)
• formatNumber(SUM(SUM(0.0,...));2)

but it is still the same error, I guess that tries of addition didn't change NaN to 0.0.

The effect that I would like to get is for example omitting NaN cases by formatNumber() function.

the problem is that in the moment you get a NULL, being nothing, you can't add, modify, etc...

Maybe in the quey when you get the results, add something like CASE WHEN column IS NULL then 0 ELSE column END

HTH

This would be my suggested solution too!

@Guillermo Gost ,@janpeter.van.d 

Thank you for your advice. Of course it sounds logical, but I still receive the same error.

Maybe something is wrong with my syntax?

The logic itselfs seems alright to me. However, I don't think you can use PQL in this Action Flow fields (sorry, I missed the part that you mentioned that). Since Action Flows are an incorporated different product (google on Integromat), the syntax is differently.

To do a sum here, use the syntax mentioned under Action Flows: Math functions (celonis.com), being sum ([array of values]), sum(value1;value2; ...)

I hope this helps!

@janpeter.van.d, this time I'm not sure if I understand.

My conclusions from your answers above:

1. I can't use here, in the formula of the column of the Query data bubble this function formatNumber. Right?
2. You suggested the usage of the sum function described here Math functions (celonis.com). Is this something different that I've done already?

Hi Rafal,

Sorry for the confusion. Both formatNumber() and sum() are supported in Action Flows, since they are specified in the math functions. CASE WHEN statements however are PQL/Vertica and therefore not supported. So yes, you can do summations combined with formatNumber, but the part that fails is the CASE WHEN statement.

You can use other functions however that are similar:

• if()
• ifempty()

I hope this clarifies

I was suggesting to put that CASE WHEN in the Query component

but, if that is not an option, it seems that with a Switch component you can implement it (I haven't tested, but it looks promising)

@janpeter.van.d, it didn't help. Still the same error. And I had this CASE WHEN construction before in this Query Data module and it was okay. Only the connection of this and this formatNumber() causes a problem.

@Guillermo Gost, all this takes place in the "Query data" module. Inside a window with a formula for one of columns.

Hi Rafal,

Sorry, I wasn't aware that you was working in the Query Data module. Since this is a module created by Celonis itself, if can actually handle PQL queries.

I do not have experience with this situation, but maybe the PQL equivalent of formatNumber could help here? See ROUND (celonis.com) for more information.

Yes, I tried with ROUND(), but having eg. 12345 number, the result of the ROUND(12345,2) will be still 12345 (I would like to get 12345.00). Moreover, with ROUND() there are no separators, the final result I would like to get is 12,345.00.

Thank you guys, the solution with separated module in which I modify values which are for sure not NaNs helped.