Aggregation Issue

[#1]

Hi Qlik Helpers,

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')

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 wronge if both treatments are selected.

Appreciate all your help.

Regards

[kumarnatarajan]

Hi ,

Check the Attached file

[#3]

Hi Kumar,

Thanks for your help.

However, the initial issue still stands:

1 - I need to count the distinct (N)aïve (Patient Status) patients and their lenght on therapy (Treatment Duration) by therapy type (Treatment Type) (table 1 in the example attached), provided he following restrictions:

     a) patient are classified as (N)aïve (Patient Status) only on their 1st month of treatment. Over the periods ahead they are classified as (E)xperienced patients

     b) for these (N)aïve patients I need to count them over the subsequent periods of treatment (2,3,4,5.....)

2 - Your approach in table 2 reads ok for month 1, and count accuratelly naïve patients. But for the subsequent periods you are counting distinct patients no matter their status, which doesn't met the clause 1) above.

3 - On the the table 3, you can only list the number of distinct patients that are (N)aïve and that's why you only have readinds for the treatment duration '1'.

The problem continues the following way:

c) taking this initial set of patients that are Naïve in period 1 of treatment per treatment type, about are they progressing in period 2, 3, ,4....,n?

That's the question I would like to anwser

Appreciate all your help

Regards

[kumarnatarajan]

Hi,

If i understand you correctly, Please check the attachment file

Thank You,

Kumar N

[SunilChauhan]

use

below expression

if([Treatment Duration]<=1 ,aggr(count(

                    {<

                    [Patient Status]= {'N'},

                    PatientID=P({<[Patient Status]= {'N'}>})

                    >}

                    DISTINCT

                    PatientID)

, [Treatment Duration], [Treatment Type]),aggr(count(

                    {<

                    [Patient Status]= {'E'},

                    PatientID=P({<[Patient Status]= {'N'}>})

                    >}

                    DISTINCT

                    PatientID)

, [Treatment Duration], [Treatment Type]))

hope this helps

[#6]

Hi Kumar,

Definatelly you're close to the solution, but still the results are not absolute accurate:

1 - You the expression you've designed, we have precise results for Month 1 for Naïve patients by therapy, which you can check by selecting each one of the therapies on the listbox. You conclude you have no changes on month 1 (naïve population of patients).

     For Therapy 1 the result is 973 and for therapy 2 the reading is 600. Both results are ok.

2 - 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.

For example:

     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

So, the initial issue still remains more or less the same, but now the wrong results happen to appear from period 2 onwards.

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.

All your help is very much appreciated.

Regards

Message was edited by: Joao Morais

[#7]

Hi SK,

Thanks for your help.

Your solution just needs a minor adjustment in the if condition (Treatment Duration must be equal to 1).

Anyway thanks for your valuable contribution.

Regards

[#8]

Hi SK C,

Definatelly you're close to the solution, but still the results are not absolute accurate:

1 - You the expression you've designed, we have precise results for Month 1 for Naïve patients by therapy, which you can check by selecting each one of the therapies on the listbox. You conclude you have no changes on month 1 (naïve population of patients).

     For Therapy 1 the result is 973 and for therapy 2 the reading is 600. Both results are ok.

2 - 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.

For example:

     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

So, the initial issue still remains more or less the same, but now the wrong results happen to appear from period 2 onwards.

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.

All your help is very much appreciated.

Regards

Message was edited by: Joao Morais

[#9]

Anyone's help will be very much apppreciated!!!

Regards