Set Analysis Formula Not Working for Common Calendar at Month Or Year Level

[#1]

This my first post in this forum/community, so hopefully I'm posting in the right area. If not could you please direct me to the right sub place/group.

Problem

I have several metrics coming from different systems that have different dates and quantity columns to summarize. I have joined 3 data sets at the patient level (lowest grain), and I'm using a bridge table called "Har_Link" to join the 3 data sets and use a common calendar. I can differentiate between the 3 data sets using field called "PatientType" Screen Shot 1 Below.

I got the logic from a QV Cook Book example (see attached QVW). This might provide additional context of what we are trying to do.

Formulas

Two of the three data sets work perfectly to summarize up the metric using this simple set analysis statement:

Example

sum({<PatientType={'2'}>} Admissions)

But I am having trouble with the second data set "PatientType = 1" because this data set counts 1 patient day for each date the patient is in the hospital. So if the patient is in the hospital from November 1-November 8, each day they have a record in the patient days table with a value of 1.

To summarize this table I'm using this set analysis formula which I thought would help do the summarization based on a range of days for the dynamic period I'm looking at...

Problem Formula

sum({<PatientType={'1'}, [Post Date (Bed Charge)] = {'>=$(=MIN({$} Date))<=$(=MAX({$} Date))'}>} [Patient Days])

However, it doesn't properly summarize the "Patient Days" field when more than one date is selected using the common date table, it appears to be duplicating the data in some fashion.

Me and another colleague have tried several things and nothing is working. Hopefully this enough information for you to understand what we are trying to do. If not please let me know and I can provide more information. Being it's health care data, I'm trying to limit the amount of sensitive data in the screenshots, but I'll provide as much information as I can.

Thanks,

Ronald

Screen Shot 1 - Common Calendar with Bridge

Screen Shot 2 - First Table is Not displaying correctly when using the Common Calendar Date. It should look like the 2nd Table below (which is using the date from the Patient Days table).

Screen Shot 3 - First Table works when I limit to only November 1st a single day.

[swuehl]

I haven't fully understood how field [Post Date (Bed Charge)] is related to the other fields. Could you detail on how your three tables were joined and how you want the bridge table to support your model ( I know the tutorial, just haven't fully understood how your model was built upon this)?

Then, there might be a problem with date selection field format:

Dates in Set Analysis

i.e. try something like

sum({<PatientType={'1'}, [Post Date (Bed Charge)] = {'>=$(=Date(MIN({$} Date),'YYYY-MM-D')
)<=$(=Date(MAX({$} Date),'YYYY-MM-DD'))'}>} [Patient Days])

If possible, try to set up a small sample QVW (with mock up data) that demonstrates your setting and your issue. It's much easier to understand and help approriately.

Regards,

Stefan

P.S. In general, one of the better first posts I've seen. Place could be correct, as it is unclear if your issue is about 'scripting' the data model (in script editor) or front end (set expression)

[#3]

Hi Stefan,

Thanks for following up. I collapsed all the fields that are not needed, and took a screen shot of my full data model.

I tried the date formating thing it didn't seem to help, the results stayed the same. I'll try to put together a mockup of the issue, without any patient data, but it might take a bit longer, and if I don't get interrupted.

Regarding [Post Date (Bed Charge)], the significance of this date is that it's the date from the table PatientDays which has a record that is counted for each day the patient is in the hospital.  I used the [Post Date (Bed Charge)] date in the table above to illustrate that when the date from the source table is used the results show correctly, but I want to use the "Date" field from the common calendar table so that I can compare data sets and analysis across a common date field.

Hopefully that makes a bit more sense, and maybe the data model below will help also.

I'll work on the QVW with mock up data.

Ronald

[swuehl]

I think I start to understand. In contrary to the sample tutorial, Hospital Account ID is not a primary key for Patient Days tables. So when you select a date range, there are several Dates linking to the same Account ID. And in the Patient Days tables, there are still multiple records per Account ID, too, one for each Post Date.

To resolve this, I think you just need to count({<PatientType = {1}>} PatientType) to get your patitent days correct or add the patient days counter to the  Har_Link table (potentially dismissing the Patient Days table completely.

Hope this helps,

Stefan

[#5]

Hi Stefan,

Thanks for your help,  th issue with doing a count({<PatientType = {1}>} PatientType) to preplays the SUM for Patient Days, is that Patient Days has negative and positive values. So to get the right value, we need to SUM.

Unfortunately, I ran out of time of this issue, I have saved all the code, but had to revert back to a higher grain of the data, and deliver the dashboard. If more time is allocated to the project, I'll circle back.

Thanks,

Ronald

[swuehl]

No problem.

If you need some more help, it would be good to know how the PatientDays table look like and how the relation to Har_Link is built.

If possible, a small sample QVW that represents your setting would be good (could contain mock up data).

Regards,

Stefan

[#7]

Thanks! If I do more digging on it I'll definately post a sample QVW with mock data.

Ronald