6 Replies Latest reply on Sep 23, 2019 10:22 AM by Kaz Shakir

# Calculate days worked with a condition

I'm trying to calculate the average of encounters a provider has in a month based on the number of days he worked.  I'm counting the day when an encounter is found, except that I don't want to include the days when he has 1 or 2 encounters only (obviously an error) as it will skew his average.  I've been doing a calculated field to "count distinct the date of encounter with the condition that the number of records is >1" as the number of days worked.  But I'm not getting my desired result.  It's still giving the number including those days with 1 encounter.  Example below, June should have 19 days worked only, but still getting 20.  What am I missing?

Cynthia

• ###### 1. Re: Calculate days worked with a condition

Hi Cynthia,

It looks like it's looking at the day datepart - ie all the 1sts are calculated the same. 1st June, 1st July.

Try repacing your DAY(end timestamp) with datetrunc('day', end timestamp).

Thanks,

Mavis

• ###### 2. Re: Calculate days worked with a condition

Hi Mavis, I tried doing it and it's still giving adding the date with 1 encounter.

• ###### 3. Re: Calculate days worked with a condition

Cynthia,

The reason that's not working is that your LOD expression, {FIXED ...} needs to also include the provider.  Change the formula to look like this:

COUNTD(

IF {FIXED [Rendering Provider], DAY([Enc Timestamp]): SUM(Number of records)} > 1

THEN DAY([Enc Timestamp]) END)

The way you have the formula currently, in the IF statement, it's calculating the sum of records across all your providers, not just the the one provider you might be showing in the viz, or in that section of the viz.

Thanks,

Kaz.

1 of 1 people found this helpful
• ###### 4. Re: Calculate days worked with a condition

Hi Kaz, it's still counting the days with 1 encounter even with the rendering provider included in the LOD.

• ###### 5. Re: Calculate days worked with a condition

I played around with the solution you gave me and removed the DAY function from the equation, and it worked!  Thank you Kaz for you help!

Final solution:

• ###### 6. Re: Calculate days worked with a condition

Glad you were able to work out a solution.

Kaz.