Hmmmm.... not really sure as I expect Celonis will group KPI based on the dimension... but, that said, try

PU_AVG(DOMAIN_TABLE(ROUND_WEEK("TABLE"."TIMESTAMP")), COUNT("TABLE"."NUMBER" || "TABLE"."FIELD"))

HTH, best luck!

Same issue, it is not possible to use COUNT inside a PU function unfortunately

Ups... then use a PU_COUNT(CONSTANT, "TABLE"."NUMBER" || "TABLE"."FIELD") instead of the count

(not sure about the CONSTANT, maybe is other function)

Hi @irfan.suria12 

thanks for sharing this use case.

I think you can use PU functions for it. My idea would be the following: (1) Count the number of values per week and (2) divide it by the number of days of that week.

(1) The first part would be a simple PU_COUNT with a DOMAIN_TABLE: PU_COUNT ( DOMAIN_TABLE ( ROUND_WEEK( "Table"."Timestamp" ) ), "TABLE"."NUMBER" || "TABLE"."FIELD" )

(2) For the second part it depends if you always want to take 5 or 7 days into account, even if you don't have any records for some days, or if you want to take the number of days for which you actually have records. In the first case just divide it by 5 or 7, in he second case you can calculate how many days you have in each particular week: PU_COUNT_DISTINCT ( DOMAIN_TABLE ( ROUND_WEEK( "Table"."Timestamp" ) ), ROUND_DAY( "Table"."Timestamp" ) )

So the solution for the "week average" KPI would be one of those:

• PU_COUNT ( DOMAIN_TABLE ( ROUND_WEEK( "Table"."Timestamp" ) ), "TABLE"."NUMBER" || "TABLE"."FIELD" ) / 5
• PU_COUNT ( DOMAIN_TABLE ( ROUND_WEEK( "Table"."Timestamp" ) ), "TABLE"."NUMBER" || "TABLE"."FIELD" ) / PU_COUNT_DISTINCT ( DOMAIN_TABLE ( ROUND_WEEK( "Table"."Timestamp" ) ), ROUND_DAY( "Table"."Timestamp" ) )

The dimension and the first KPI remain as they are.

Note that in contrast to standard aggregations, PU functions don't take filters into account, which is why you might want to use FILTER_TO_NULL around the second arguments of all PU functions to get the also consistent values with your first KPI.

Best

David

David, what an honor!

And I have expected nothing else from you - it works like a charm!

Just a minor addition though: For the first option, I would need to restrict the PU_COUNT to the 5 days as well, otherwise it will take the count of the whole 7-days week and then the average would not be correct.

So my final formula is the following (restricted to labour week, on weekends the KPI is nulled as requested by the business):

CASE WHEN DAY_OF_WEEK ( "Table"."Timestamp" ) IN (6, 0) THEN NULL ELSE

PU_COUNT ( DOMAIN_TABLE ( ROUND_WEEK( "Table"."Timestamp" )), "TABLE"."NUMBER" || "TABLE"."FIELD", DAY_OF_WEEK ( "Table"."Timestamp" ) NOT IN (6, 0)) / 5

END

Best

Irfan