Hello, had to create a new account, hadn’t used the old one in years. I’m facing a problem today that I could use some assistance with.
My date looks like this:
Service / Type / <31 columns showing totals by date for previous month>
I attached a small snippet of data.
The numbers in the date columns are a distinct count of patients…there are multiples in the dataset, I want them counted only once in each level of detail.
I accomplished this via: =Count([Patient]) ForEach ([Service])
Now, I am trying to create a Monthly Daily Average for each level of detail, on a separate sheet.
This formula got me close to an accurate average: =Count([Patient];All)
ForEach ([Service]) / Count([Date])
The denominator is fine, but the numerator is counting the duplicate patients per day.
Any advice on how to make this calculation work?
Thanks…
Bill
2/1/2018 2/2/2018 2/3/2018 2/4/2018 2/5/2018
Hospitalist A Inpatient 64 62 71 63 72
Hospitalist A Observation 12 11 9 10 7
Hospitalist B Inpatient 24 20 19 22 23
Hospitalist B Observation 1 2
Hospitalist C Inpatient 11 9 9 16 14
Hospitalist C Observation 1 1
Hospitalist D Inpatient 17 17 16 16 13
Hospitalist D Observation 2
wjm (BOB member since 2018-03-29)