I'm having an issue with an aggregation expression that doesn't fetch the expected results.
Issue summary:
1 - I'm looking at patients behaviour related to a treatment type (T1 and T2);
2 - Patients may have 2 different categories or status: Naive patients ('N') and experienced patients ('E')
4 - I'm interested to capture the Naïve patients only (Patient Status ='N') and understand for how many periods their treatment lasts.
3 - I want to count the distinct patients distribution over the treatment lifespan (Treatment Duration), by each Treatment Type and provided they have a record as Naïve patients ('N') at one of the periods available.
The expressions reads ok if I select a single treatment type but it is wrong if both or all treatments are selected.
Example:
- For Therapy 1 the result is 973 and for therapy 2 the reading is 600. Both results are ok.
- But take a close look to what happens on month 2 when you select each of the therapies available in the listbox and when you selec none of them.
- In the pivot table you'll find the different results that are fetched.
- If you select T1 (on therapies listbox) you have precise results all over the treatment duration periods, i.e.,
M1 - 973
M2 - 877
M3 - 834
, but if you select both therapies, or none, the result that is fetched for T1 is the following:
M1 - 973
M2 - 887
M3 - 843
My believe the reason for this behaviour lies on the P() parameter within the set analysis which does not take in consideration, for the set members, the therapy segments it should consider to form each bucket of patients.
Could you please take a look to the qvw attached and advise what could be a possible solution for this?
In your expression Expected Results you explicitly set a filter for Treatment Type. In your Current Results you don't. So in your Current Results the filter PatientID=P({<[Patient Status]={'N'}) doesn't consider Treatment type. A set analysis expression is calculated per chart, not per row/column. The set analysis expression can't dynamically use the dimension value. Try for example adding this expression to your chart:
You'll see that the numbers in the T1 column are exactly the difference between Current Results and Expected Results. And for the T2 column they are the same as the Expected Results.
How do you solve it? Well, you could add a nested if to check for Treatment Type like you're doing in the Expected Results expression. But if Expected Results is correct, why create another basically identical expression?
In the Expected Results column the results are accurate because I hardcoded the Treatment Type (T1 & T2) in the expression.
But in fact I must do it dynamically and get the reference to the Treatment Type values out of the expression.
When I try it I have duplications, which you can see in the column Current Results, because of the P() clause which are selecting the naive patients once per chart (Patient Status = N).
These sets of patients is what i'm trying to count through treatment duration '1' onwards, and chech how many of them stay on treatment in the periods ahead.
I understood your way of thinking but how to get the expression to provide the 'Expected Results' values, no matter the type of treatment dimension?
Can you give me a clue of how the expression must look like in order to:
1 - Identify the naive patients (Patient Status = 'N' and Treatment Duration = '1');
2 - This identification must be reached taking the Treatment Type in consideration;
3 - For each of the patient sets per Treatment Type, count all of those that remain active patients within the treatment (there must be a record from Treatment Duration '2' onwards)
Can you give me a clue of how the expression must look like in order to:
1 - Identify the naive patients (Patient Status = 'N' and Treatment Duration = '1');
2 - This identification must be reached taking the Treatment Type in consideration;
3 - For each of the patient sets per Treatment Type, count all of those that remain active patients within the treatment (there must be a record from Treatment Duration '2' onwards)
Let's try a different trick. See attached qvw. I've added a field TTNaive and use that instead of the field Treatement Type in the chart. The Current Results expression then becomes Count({<[Treatment Duration]={'>0'}>} DISTINCT PatientID). This should also allow for more treatment types than just the two you have in your data now.
I have follow a similar strategy with a couple of fundamental differences, that are in fact constrainsts of the real situation.
In fact, the reference to the treatment types cannot be hardcoded in the expression, because there are more than 300 distinct treatments and so it is not practical to pursue in that direction.
So the references ti the treatments within the set analysis must be dynamic, so the expression is prepared to calculate any data for whatever treatment types selected.
Additionally, I want to show only 5 treatments at a time, which means that, if no treatment is selected then the top5 in terms of patient adoption will be shown.
If some treatment or treatments are selected, then those will be shown at maximum of 5.
Saying that, it's pretty much the same expression design you've reached.
1 - I've created a variable named Test to store all the Treatment Type selections
2 - Then, the expression creates the patient set based on the equalization of the Trst variable members to the treatment Type dimension.
Please take a look the attached qvw and you easily understand what I mean.
Anyway, thanks so much for your interest and help.
