how to calculate days in between dates in straight table? urgent help please

[#1]

Hi,

I have data as below. I need to calculate days between highlighted dates. please can anyone suggest me how to do this?

the condition is when STAGE2 =CC and STAGE2=MR then need to calculate days between DATE 28/01/2015 12:00 and 01/04/2015 18:28                                                                                                 

ID	STAGE1	STAGE2	DATE
43998	CM	AP	18/05/2015 15:32
43998	PD	CM	24/04/2015 14:24
43998	DTD	PD	08/04/2015 14:11
43998	MR	C	08/04/2015 14:10
43998	D	MR	01/04/2015 18:28
43998	DA	D	01/04/2015 18:01
43998	RE	DA	28/03/2015 17:14
43998	RA	RE	28/03/2015 16:38
43998	CP	RA	13/03/2015 16:49
43998	CS	CP	27/02/2015 13:30
43998	QR	CS	27/02/2015 13:29
43998	EL	QR	27/02/2015 13:28
43998	TA	EL	11/02/2015 14:51
43998	CC	TA	11/02/2015 14:51
43998	CC	CC	28/01/2015 13:30
43998	LG	CC	28/01/2015 12:00

Thanks.

[anlonghi2]

Hi Amelia

please look at the attached doc: the table show days between dates where the condition is true.

Best regards

Andrea

[sunny_talwar]

What exactly is not working?

[#4]

Thanks. when I sued the expression entire table is showing as blank. please help

[#5]

when we checked for individual IDs values are not showing correct. do I need to change anything?

[#6]

Thanks. I have used this table showing all blanks.

[Digvijay_Singh]

you need to share sample data where it is not working, what I proposed was based on earlier sample, where set analysis was used to identified min date where Stage2 is MR and CC and subtracted the resulted date as you needed. By mistake I used max for one of the date, pl check if that is a problem or better share the sample where it is not working.

[anlonghi2]

Hello Amelia,

I made some changes to the doc (script and table).

Please let me know if now it's ok you.

Best regards

Andrea

[#9]

Thanks. values are not showing correct. when I used for all IDs

[anlonghi2]

Amelia, I duplicated your data and the doc seems to work. Look at the following print screen that show the correct number of days between dates.

Have you modified your load script using mine as example ?

[#11]

Thanks. Yes I have used the same script. number of days is showing correct now when we checked for individual IDs.

if I want to calculate Median how to do this?

suppose if I want to check the Median of IDs 32432 and 32325

ID 32432 have number of days 314

ID 32325 have number of days 415

for above Median is 364.5 (calculated in excel)

how to calculate Median for all IDs in straight table. please help.