Repeated measure analysis

[#1]

Hi,

I am new to qlik and need some help.

I have a table with repeated measures.

Each row in the table contains data of a single measure for a single ID.

Example:

ID	Date	Stage	Total
171148,00	08/10/2010	Assessment	7,80
171148,00	10/01/2011	Demission	10,30
171149,00	09/09/2010	Assessment	19,10
171149,00	17/01/2011	Demission	19,70
171150,00	06/09/2010	Assessment	12,80
171150,00	17/01/2011	Demission	9,40
171151,00	25/08/2010	Assessment	6,20
171152,00	09/11/2010	Assessment	18,50
171152,00	12/01/2011	Assessment	14,70
171152,00	15/02/2012	Intermediate	10,30
171153,00	09/11/2010	Assessment	13,80
171153,00	13/01/2011	Assessment	13,20
171153,00	15/02/2012	Demission	14,10
171153,00	20/05/2014	Demission	15,30
171154,00	18/10/2010	Assessment	12,90
171155,00	25/08/2010	Assessment	13,30
171156,00	01/10/2010	Assessment	20,60
171157,00	19/10/2010	Assessment	23,50
171157,00	21/02/2012	Intermediate	30,30
171157,00	09/05/2012	Intermediate	22,40
171158,00	30/11/2010	Assessment	17,10
171159,00	01/12/2010	Assessment	15,60
171159,00	09/02/2011	Intermediate	7,40
171159,00	06/10/2011	Intermediate	7,10
171160,00	21/02/2010	Assessment	23,50
171160,00	10/02/2011	Intermediate	3,80
171161,00	24/08/2010	Assessment	18,50
171162,00	11/01/2010	Assessment	11,50
171162,00	15/03/2010	Demission	5,90
171163,00	11/01/2010	Assessment	10,60
171163,00	15/03/2010	Demission	1,80
171164,00	07/12/2010	Assessment	21,80
171165,00	27/10/2006	Assessment	21,80
171165,00	21/12/2010	Intermediate	10,60
171165,00	19/07/2011	Intermediate	10,60
171165,00	06/03/2012	Intermediate	12,60
171165,00	29/01/2013	Demission	11,20
171166,00	30/11/2010	Assessment	13,80
171167,00	28/12/2010	Assessment	21,50
171167,00	19/01/2014	Intermediate	24,70
171167,00	06/05/2014	Intermediate	18,80
171179,00	09/09/2010	Assessment	11,50
171180,00	27/09/2010	Assessment	20,00
171181,00	09/09/2010	Assessment	10,90
171181,00	10/01/2011	Demission	9,40
171182,00	06/09/2010	Assessment	15,90
171182,00	10/01/2011	Demission	5,60
171183,00	06/09/2010	Assessment	9,70
171183,00	10/01/2011	Demission	10,90

I would like to calculate for each single ID the difference between the Totals of the first measurement and the last measurement where there is repeated measures. The condition for the first measurement is that it should be the first measure when stage is 'Assessment' and for the last measurement that it should be the last measure when stage is either 'Intermediate' or 'Demission'.

Furthermore, I also need the difference in between the Date of the first measurement and the last measurement in days.

Thanks

Daniel de Wet

[Anonymous]

Hello Daniel, try a straight table with ID as dimension and with 3 expressions:

1)

sum

({<Stage={'Assessment'},Date={"$(=Min(Date))"}>}TotalN)

2)

sum

({<Stage={'Demission', 'Intermediate'}, Date={"$(=max(Date))"}>}TotalN)

3)

Column

(1)- Column(2)

TotalN is oyur Total filed, but I have some issues because Total is a jey word

KR

Elena

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[francoiscave]

Hi Daniel,

The best document to manipulate Set Analysis:

Set Analysis: syntaxes, examples

By Fabrice Aunez.

It will be helpful !

François

[#5]

Thanks for the Reply Elena

[#6]

Thanks Gysbert!!!

[#7]

Thanks François!

Daniel