How to calculate the weekly average of the daily unique values?

Hello E N,

We hope this post finds you well and you are staying safe.

We are since we haven’t heard from you for 5 calendar days, we wanted to put this back on your radar. Have you been able to review our last post? Is there anything you need clarified?

When you have a free moment, we would appreciate your follow-up. If this is already resolved, no further action from your side is required and we will change the case to Closed after another 5 days.

Once a case is moved to Closed, there is still a grace period of 1 calendar day before the final closure. This means that the case will reactivate when a reply is received within this period. Our Customer Support Team is available 24/7 and stand ready to assist you.

Thank you in advance for your time, and I hope to hear from you soon.

Feel free to visit our Community to track your request.

Best regards,

The Data Science Team

Hi Tina , sorry the delay, a bit swamped.

Ok lets see:

• first, this was needed for an internal project, but since then it is no longer needed (they decided to do it with python scripts)
• Still, it seems like a typical situation, and I'd like to have it as one of my "patterns"
• The problem:
  • Each case has activities each day, done by a pool of workers
  • They want to calculate the daily number of workers. This is easy as it is just the count_distinct of users.
  • But now, they want, each week, to know what is the AVERAGE DAILY number of workers.
    • I.e. in a week, monday 3 different workers work, tuesday 4, wednesday 5, thusrday 6, friday 3
    • So the average should be 3+4+5+6+3 / 5
    • But keep in mind that the same user can work several days of the week, so COUNT_DISTINCT doesn't give you the right answer.

In my particular situation, I have a case table

There I have:

• Registration date - when was created the case
• User - who created that case
• RegistrationType - type of registration

And I want, per week, per type of registration, calculate the average daily number of workers.

My last try was this PU

SUM(

 PU_COUNT_DISTINCT(DOMAIN_TABLE(ROUND_DAY("_RN_Digitalizacion"."FECHAREGISTRO"),"_RN_Digitalizacion"."TIPODIGITACION"),

"_RN_Digitalizacion"."USUARIOREGISTRO"

)

As you see, I try to get the COUNT_DISTINCT of User per day, and then sum it (eventually to get the average I will divide by the number of working days that week)

Remember, I am working only with the case table.

If I create an olap like this:

TABLE ( 

 ROUND_WEEK("_RN_Digitalizacion"."FECHAREGISTRO") 

AS "Round_Week(FECHAREGISTRO)"

FORMAT "%Y-%m-%d"

,

"_RN_Digitalizacion"."TIPODIGITACION" 

AS "TIPODIGITACION"

,

COUNT_TABLE("_RN_Digitalizacion") 

AS "Case count"

FORMAT ",f"

,

COUNT(DISTINCT "_RN_Digitalizacion"."USUARIOREGISTRO") 

AS "Count Distinct(USUARIOREGISTRO)"

,

SUM(

 PU_COUNT_DISTINCT(DOMAIN_TABLE(ROUND_DAY("_RN_Digitalizacion"."FECHAREGISTRO"),"_RN_Digitalizacion"."TIPODIGITACION"),

"_RN_Digitalizacion"."USUARIOREGISTRO"

)

) 

AS "Sum PU CountDistinct per day"  

) NOLIMIT;

I get crazy values

for example in the first line, the expected "Average daily number of workers" gives me more than the number of cases in that week!!! (well, no just more, like a crazy more)

For reference, the whole case table has:

• 2.34 M records
• 541 different workers

I am at a loss here.... so any clue will be really appreciated

Hi Guillermo,

What do your numbers look like without:

COUNT_TABLE("_RN_Digitalizacion") 

AS "Case count"

FORMAT ",f"

,

COUNT(DISTINCT "_RN_Digitalizacion"."USUARIOREGISTRO") 

AS "Count Distinct(USUARIOREGISTRO)"

Included in your OLAP Table?

I believe the issue is with your DOMAIN_TABLE. Including other columns in the table not included in the DOMAIN_TABLE() may cause aggregation errors.

Please review the documentation on DOMAIN_TABLE for further understanding of DOMAIN_TABLE functionality.

https://docs.celonis.com/en/domain_table.html

Best Regards,

The Data Science Team

Tried, removed both,.... still the same. :(

Ok, no worries. I will keep trying things for time to time... now it is personal :D

(Besides, summer is low session with projects and customers, so I guess I will have more time...)

Nevertheless, thanks a lot for trying!