Hello everyone, I am trying to count distinct number of shipment ids from ShipmentTable and i want to count only the ones occured in 2023. Celonis doesnt allow me to use count distinct in case when function.How can i write it,thanks a lot

Hi @emre.karad,

You should use a PU-function here.

In the 4th and 5th example in the docs (see PU_COUNT_DISTINCT (celonis.com)), you get the following statement:

PU_COUNT_DISTINCT ( "companyDetail" , "caseTable"."value" , "caseTable"."caseID" > 3 )

Here you have 3 elements:

• "ComanyDetail" should be the table name to which you want to project the number of shipments (for instance the Order Table)
• "caseTable"."value" should be your "ShipmentTable"."ShipmentID" column, the distinct count is applied on this column
• caseTable"."caseID" > 3 should be your filter, for instance YEAR( "ShipmentTable"."ShipmentDate") = 2023.

I hope this helps!