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.
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
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!