Average Daily Census - QlikSense

[jlaudner]

I am using QlikSense.

I am trying to make an object that would show an Agency's Average Daily Census for their Residential Programs.

My Dimensions are Facility Name and Calendar Month-Year, but I don't know how to set up the Measure.

Sample Data:

Facility Name          Patient#          Admission Date          Discharge Date

A                              1                    11/20/2017                  12/12/2017

A                              2                    11/23/2017                  1/17/2017

A                              3                    12/3/2017                    NULL

So for the month of December 2017 my Average Daily Census for Facility A should be

Because:

Patient A is 12, for 12/1 through 12/12

Patient B is 31, for 12/1 through 12/31 because they were in the Facility the whole month

Patient C is 28, for 12/3 through 12/31 because they are still active

This totals 71 Census Days, which then divided by 31 days in December, my Average Daily Census is 2.29.

Thank you all for your help!