4 Replies Latest reply: Aug 1, 2018 3:47 PM by Phil Detweiler RSS

    Dimension not always valued causes gaps

    Phil Detweiler

      Hello. I'm new to Qlik Sense and have a general question related to using a dimension that may not always be represented causing gaps.

       

      Load script

      In my load script there are two events that have a visit day assigned to indicate which day of the hospital stay this event occurred on. In some cases, a visit day may not have either of these events.

       

      if(

      [Med Task Performed Dtm] - [%MedsEncounterAdmissionDate])+1 > 0,

      [Med Task Performed Dtm] - [%MedsEncounterAdmissionDate])+1

      ) as "MedTaskPerformedVisitDay"  // returns a number

       

      if([Px Coded Seq] = '1',

      ([Px Date] - [%PX_EncounterAdmissionDate])+1

      ) as "PrincipalPxVisitDay" // returns a number


      Table dimension

      Using a dimension like this plots a measure by the visit day a med was given.


      ='Visit Day '&MedTaskPerformedVisitDay  


      Problem

      I highlighted the visit day background by using this but of course if no meds were given on the particular visit day that matches the procedure's visit day, this method fails.


      If(MedTaskPerformedVisitDay=PrincipalPxVisitDay,

      red()

      )

       

       

      I"m pretty sure I need to approach this differently, perhaps with a table containing all the possible visit days (something similar to the master calendar) and then join these visit day fields to this table? I'm just getting started so I'm not sure if I'm thinking about this correctly or if there is a better way to do this type of thing.

       

      Thanks so much for any guidance, much appreciated!