NULL handling in Average formula:Hi all I want to create a (weighted) average of 3 already predefined KPIs which create a single number (percentage value).Now I want to create a weighted average of those three which is handling NULL values.

Hi Manuel,

I think only way is using CASE WHENs to cover this situation.

I my opinion this should be managable as you just have to add it for each KPI.

I think it took more time to write this question to celopeers than adding the CASE WHENs ;)

((CASE WHEN ISNULL(KPI_A)=1 THEN 0 ELSE KPI_A END *1)+(CASE WHEN ISNULL(KPI_B)=1 THEN 0 ELSE KPI_B END*2)+(CASE WHEN ISNULL(KPI_C)=1 THEN 0 ELSE KPI_C END*1))/3

BR

Dennis

Thanks Dennis.

I made the example a minimal one ;) my reality has more than 3 KPIs and if I am not mistaken this becomes a factorial problem to cover the case that each KPI (or multiple) are NULL. So 5! = 120, thats why I was looking for a more elegant solution.

hmm not sure with your thoughts on factorial problem.

As you are checking NULL value for each KPI individually you need as many CASE WHEN statements as KPIs you bring into the logic. It shouldn't be an issue when multiple KPIs will have a NULL value but maybe I am missing a point here.

BR

Dennis

Hi @manuel.wetze,

In this case wouldn't the COALESCE function be handy perhaps? I guess that it won't fully solve your problem, but it will cut the syntax in half and it will be better to read.

There is also some documentation with an example how it would look like instead of doing CASE WHEN

https://docs.celonis.com/en/pql-performance-optimization-guide.html#idm45262528984736

Kind regards,

Sverre Klein

Hi both,

thanks for your hints. I actually made it work combinging both and having Dennis challenge in mind about the factoral complexity.

In short: COALESCE is helpful to create the numerator. But the nominator needs to be created by hand.

For the sake of completeness the following PQL fulfills my needs:

( -- numerator: summing up all KPIs or 0 (if they are NULL) and multiply by their weight

  1*COALESCE(KPI("KPI_A"),0)

 +2*COALESCE(KPI("KPI_B"),0)

+1*COALESCE(KPI("KPI_C"),0)

)

/

(-- denominator: sum up all weights or 0 (if corresponding KPI is NULL)

  (CASE WHEN KPI("KPI_A") IS NOT NULL THEN 1 ELSE 0 END)

   +(CASE WHEN KPI("KPI_B") IS NOT NULL THEN 2 ELSE 0 END)

   + (CASE WHEN KPI("KPI_C") IS NOT NULL THEN 1 ELSE 0 END)

Hi @manuel.wetze,

Another solution would be to remap the nulls to 0s. Of course it highly depends on the specific use case you are working on, but in certain scenarios, I think this could be a very efficient solution: remap_ints(KPI("KPI_A"), [NULL,0])

One advantage of this, is that you can handle multiple "replacements" at once, therefore for more complex situations this can simplify your code significantly.

One thing to consider is whether to handle these null exceptions in the aggregated KPI, or in the "sub KPIs" (but that might be not feasable if you would like to visualize the nulls as exceptions in the sub KPIs).

Regards,

Roland