6 Replies Latest reply: Nov 19, 2015 6:50 PM by Ronald Odiyar RSS

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

      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.



      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.



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


      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.






      Screen Shot 1 - Common Calendar with Bridge

      Common Calendar.PNG


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

      Patient Days Set Analysis.PNG


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

      Patient Days Set Analysis - Day Level.PNG

        • Re: Set Analysis Formula Not Working for Common Calendar at Month Or Year Level
          Stefan Wühl

          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.



          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)

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

              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.



              Full Data Model.png