Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Still would work, just need to add another column that increments visit day for that patient key, e.g. Pat ID + Date
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.
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.
Still would work, just need to add another column that increments visit day for that patient key, e.g. Pat ID + Date
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.