Using Set Analysis to track changes in a field over time
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.
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?
