topic Re: Dimension not always valued causes gaps in App Development

Dimension not always valued causes gaps

phildetwei — Tue, 31 Jul 2018 22:09:51 GMT

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!

Re: Dimension not always valued causes gaps

dwforest — Tue, 31 Jul 2018 23:56:21 GMT

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

Re: Dimension not always valued causes gaps

phildetwei — Wed, 01 Aug 2018 00:22:20 GMT

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.

Re: Dimension not always valued causes gaps

dwforest — Wed, 01 Aug 2018 17:48:17 GMT

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

Re: Dimension not always valued causes gaps

phildetwei — Wed, 01 Aug 2018 19:47:40 GMT

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.