Using Set Analysis to track changes in a field over time

richard_cioci — Mon, 06 Nov 2017 18:39:08 GMT

Hi Everyone,

I have a qlikview dashboard which tracks the status of patients over time, with a snapshot of their status every week. I am trying to create an expression to count the number of patients who have recently had their status change from X to Y from the previous week.

I cannot simply count the current number of status X and subtract the count of status X from the previous week because patients can both go into the bucket of status X and go out, so instead of a net +2 change, I would want it broken down into +4 went in and 2 went out for a net of +2.

What I'm trying to do is use two 'sets' within set analysis to first get a set of all current patients with status X on the max status date and then subtract the set of patients who had status X on the max status date -7 (1 week prior). Theoretically this should leave me with patients who are currently X but were not X 1 week ago. But it doesn't seem to work for me.

Here is my formula:

=(count(distinct {

<[Extract Date Serial]={"$(=Max([Extract Date Serial]))"}, [Patient Status]={'Treatment Started'}>

- <[Extract Date Serial]={"$(=Max([Extract Date Serial])-7)"}, [Patient Status]={'Treatment Started'}>

} Patient_Record_ID))

This gives me a result of 180, which is incorrect. If I check the records manually, it should say 5. Last week there were 180 total with 'Treatment Started', 5 of which changed to something else this week, but there were also 5 new 'Treatment Started' this week. So it should be +5 gain, 5 loss, for a total net change of 0. What am I doing wrong with the syntax for the set analysis using multiple sets? Is this doable? I seem to get it to work when I'm adding sets, but not with subtraction, when I do subtraction it just gives me the count of records for the first set in '<>' rather than seeming to evaluate both sets and get the difference.

Thoughts?

P.S.

The two sets separated into count formulas provides the following:

=(count(distinct {<[Extract Date Serial]={"$(=Max([Extract Date Serial])-7)"}, [Patient Status]={'Treatment Started'}>

} Patient_Record_ID))

=180

=(count(distinct {<[Extract Date Serial]={"$(=Max([Extract Date Serial]))"}, [Patient Status]={'Treatment Started'}>

} Patient_Record_ID))

= 180

So even if the sets were identical then my original formula should give me 0 if no patients changed, although I know 5 did, so it should say 5.

Re: Using Set Analysis to track changes in a field over time

richard_cioci — Mon, 06 Nov 2017 18:48:25 GMT

So for some reason when I add the two sets instead of subtracting, I get what I expected, the 180 plus 5 more records, i.e. the 5 gains.

Then I can subtract a count of those who were 'Treatment Started' a week ago and I get a result of 5.

=count(distinct {<[Extract Date Serial]={"$(=Max([Extract Date Serial])-7)"}, [Patient Status]={'Treatment Started'}>+

<[Extract Date Serial]={"$(=Max([Extract Date Serial]))"}, [Patient Status]={'Treatment Started'}>

} Patient_Record_ID)

-count(distinct {<[Extract Date Serial]={"$(=Max([Extract Date Serial])-7)"}, [Patient Status]={'Treatment Started'}>

} Patient_Record_ID)

I just don't understand why the set subtraction doesn't give me the same result. Or did I do something wrong with syntax and have I not followed the sets logically?

topic Re: Using Set Analysis to track changes in a field over time in QlikView

Using Set Analysis to track changes in a field over time

Re: Using Set Analysis to track changes in a field over time