Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
phildetwei
Contributor II
Contributor II

Dimension not always valued causes gaps

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!

1 Solution

Accepted Solutions
dwforest
Specialist II
Specialist II

Still would work, just need to add another column that increments visit day for that patient key, e.g. Pat ID + Date

View solution in original post

4 Replies
dwforest
Specialist II
Specialist II

I would say, yes, the "easiest" way is to use a Master Calendar to ensure each date is represented and have the formula return 0, where appropriate.

Canonical Date

phildetwei
Contributor II
Contributor II
Author

Thanks dwforest

In this case though these are not dates but numeric values, as in visit day 1, visit day 2, etc. The actual dates used in the formula to determine the visit day could be from very different periods based on the admission/discharge dates. For example, an event occurring Jan 13 and Feb 25 would both be considered visit day 3 on a case admitted Jan 11 and Feb 23 respectively.

dwforest
Specialist II
Specialist II

Still would work, just need to add another column that increments visit day for that patient key, e.g. Pat ID + Date

phildetwei
Contributor II
Contributor II
Author

Okay thanks. I read through the link you referenced and while I sense it is very well explained, I think I lack some background needed to fully grasp what is being suggested. At this early stage though, it is very helpful to know whether the tree I'm barking up is the right one before using up too many cycles so I thank you for that direction. I think I need to do a bit more homework on some basics before I attempt this.