"Simple Ratio" query replication error

Dear @arunjit.das12 

Default saved formula 'Ratio' is described in the documentation.

KPI - Saved Formulas (celonis.com)

Ratio: AVG ( CASE WHEN {p1} THEN 1 ELSE 0 END )

So I believe your Using CASE WHEN statement is exactly same as KPI 'Ratio'.

If you are not using Single KPI component, please take care other fields (dimensions and KPIs) are affected to your calculation. Please see my blog regarding this point.

Recognize Record to Calculate KPI - Celonis hacks (kaztakata.github.io)

Best regards,

Kazuhiko

Dear @arunjit.das12 

After my previous post, I am interested in this case, so I tried in my environment.

Finally I found that KPI("Ratio", "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B') is equivalent to below formula. It should works in your environment too.

AVG(

  CASE

    WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

    WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 0

    ELSE 0

  END

)

By the way , when I tried this in Studio Analysis, I got warning message below.

"The CASE WHEN behavior change is now in effect - and this query is affected by it. Please investigate if additional NULL checks are needed. For more information, search for "CASE WHEN behavior change" in the PQL documentation."

That is derived from PQL update from this February, and currently if USER_TYPE is NULL then it is calculated as 0 (until January it is NULL).

I believe NULL is correct, so I think current Ratio KPI should be modified according to CASE WHEN behavior change above. I will send change request to service desk.

Best regards,

Kazuhiko

Hello @kazuhiko.takat12 

Thank you for the analysis!!

I tried using the CASE WHEN statement from your above post. However, it did not give me the desired result. It just got more complicated.

Please refer below for my analysis. Note- I am working with the AP model already available in Celonis.

1) Normal Simple ratio formula where NULL handling is not available

KPI("Ratio", "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B') -- 64.07%

2) Equivalent formula to simple ratio formula.

AVG(

 CASE

  WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

  WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 0

  ELSE 0

 END

) -- 65.51%

** Technically, NULL is not handled in the above statement.

3) Added null handling capability in the above equivalent query.

AVG(

CASE WHEN ISNULL("_CEL_AP_ACTIVITIES"."USER_TYPE" ) =1 THEN NULL ELSE -- null handled

      (

CASE

          WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

            WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 0     

        END

      )

     END

    ) -- 65.51%

**Still reproduced the same result as the above query(2) viz, without null handling.

4) Changed the above query (3) to replace null values to 0.

AVG(

CASE WHEN ISNULL("_CEL_AP_ACTIVITIES"."USER_TYPE" ) =1 THEN 0 ELSE

      (

CASE

          WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

            WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 0     

        END

      )

     END

    ) -- 64.07%

** Same as the simple ratio formula, which is correct as the null values are replaced with zero, same as in simple ratio.

5) Lastly, I tried using the simple case statement without null handling.

AVG(CASE WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1.0 ELSE 0.0 END) -- 65.51%, same result as query (2) and (3).

According to me query(2) and query(3) should not be giving the same result. Please correct me if I am wrong.

***********************************************************************************************************************************************************************

Additionally, I was trying to replicate the same automation rate via PU_COUNT as we were working on a dashboard where different values are aggregated to produce total opportunity cost, like "KPI("Number_of_invoices") * (1-KPI("Automation_Rate")) * KPI("COST")". For some reason the automation rate with case statements always fails to provide accurate results with (~15-20%) variance and PU_COUNT was giving results with ~1% variance, which is acceptable considering decimal values.

The PU_COUNT formula used-

 SUM (   PU_COUNT ( "BSEG" , "_CEL_AP_ACTIVITIES"."USER_TYPE" , "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' ) )

 /

 SUM (  PU_COUNT ( "BSEG" , "_CEL_AP_ACTIVITIES"."USER_TYPE" ) ) -- 65.45%, with 0.06% lower from the above null handling queries.

I have tried adding null handling capabilities to it, which gave results in the range of >70% with more than ~5% variance from the null handling queries.

***********************************************************************************************************************************************************************

Apologies for making it complicated but without full proofing this, I am not able to proceed with the analysis.

Regards - Arunjit

Dear @arunjit.das12 

Just confirmation, do you use Celonis cloud version (EMS), right ?

I tried in my environment and got different behavior about (2) I mentioned in previous reply.

Your result

(1)=(4)=64.07% (count NULL as 0)

(2)=(3)=(5)=65.51% (ignore NULL so bigger than former one)

My result

(1)=(2)=(4)=39.94% (count NULL as 0)

(3)=(5)=43.79% (ignore NULL so bigger than former one)

By the way I would like to confirm your expectation.

If there are 3 'A' record, 1 'B' record and 6 NULL record regarding USER_TYPE,

do you want to get 10% (1 / (1+3+6)) or 25% (1 / (1+3)) ?

My opinion is to get 25% (ignore NULL), so current KPI Ratio (1) is incorrectly working I think.

Normally I write like below and get my expected result. Do not use ELSE statement then write all non NULL condition by WHEN statement.

AVG(

  CASE

    WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

    WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE"  = 'B' THEN 0

  END

)

Hello @kazuhiko.takat12 

We are using the Sandbox Environment which is the cloud version.

From your comparison, I could confirm that query(2) is not working properly in my environment. I was confused with this query as NULL was not handled still it gave me the result with null handling. Thanks for that!!

For the record, my expectation is to get the 25% result in your example. NULL values should not be counted at all. However from a business point of view, this is still an opportunity for automation. So will get clarification on the same with the client.

From your query (below), I could see that NULLs are not handled. With the recent changes in Case Statement, NULLs should be counted as zero. I am just confused how it is giving me the desired result. I am not able to pin point to the fault. If you have an idea, can you please clarify how the below query is giving the desired result without NULL handling.

AVG(

 CASE

  WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

  WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 0

 END

)

Regards - Arunjit

Dear @arunjit.das12 

I do not know why we have different result, but now I got your expectation and I can answer to you.

Below is the minor change based on your (3) that was same result among us.

I recommend you not to use ELSE statement to prevent unnecessary investigation.

AVG(

  CASE

    WHEN ISNULL("_CEL_AP_ACTIVITIES"."USER_TYPE" ) = 1 THEN NULL

    WHEN "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 1

    WHEN NOT "_CEL_AP_ACTIVITIES"."USER_TYPE" = 'B' THEN 0    

  END

)

Regarding your second question about "KPI("Number_of_invoices") * (1-KPI("Automation_Rate")) * KPI("COST")", now I can not say anything because I do not know the detail of three KPIs.

As mentioned before, you are careful to use multiple table columns, that is the cause of unexpected duplicated record (read my blog for your reference).

One more general advice is to start from OLAP table without using aggregation function (AVG, SUM etc.) to check each record, then use aggregation afterward.

Best regards,

Kazuhiko