Skip to main content
Solved

Calculate AVG partitioned by week (WINDOW_AVG?)

  • February 1, 2023
  • 5 replies
  • 20 views
irfan.suria12
Level 8
Forum|alt.badge.img+3

Hi, I have the following use case and challenge:

 

Use Case: Add a line as second KPI to an existing column diagram that shows the average of a KPI value for each labor week

 

Dimension: ROUND_DAY("TABLE"."TIMESTAMP")

 

KPI 1: COUNT("TABLE"."NUMBER" || "TABLE"."FIELD") - columns

 

KPI 2: Show the average of KPI 1 from Monday - Friday for each week - line

 

My attempt:

 

WINDOW_AVG(

COUNT("TABLE"."NUMBER" || "TABLE"."FIELD")

, 0

, 5

, ORDER BY("TABLE"."TIMESTAMP")

, PARTITION BY(ROUND_WEEK("TABLE"."TIMESTAMP"))

)

 

This does not work unfortunately as I can not use the COUNT within the WINDOW_AVG function.

 

Does anyone know any alternatives?

Best answer by david.beche12

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

5 replies

G
Level 12

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!

irfan.suria12
Level 8
Forum|alt.badge.img+3
  • irfan.suria12
  • Author
  • Level 8
  • 16 replies
  • February 1, 2023

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

G
Level 12

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)

D
Level 1
Forum|alt.badge.img+2
  • david.beche12
  • Level 1
  • 240 replies
  • Answer
  • February 2, 2023

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

irfan.suria12
Level 8
Forum|alt.badge.img+3
  • irfan.suria12
  • Author
  • Level 8
  • 16 replies
  • February 3, 2023

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


Community Code of ConductAccessibility statement