Days calculated between 2 dates

[marishnagendran]

Dear All,

Lead ID	Cust	Stage	lead Status Creation Date	Lead Creation Date
1	A	Stage 1	01/01/2020	01/01/2020
1	A	Stage 2	03/01/2020	01/01/2020
1	A	Stage 3	05/01/2020	01/01/2020
2	B	Stage 1	01/01/2020	01/01/2020
2	B	Stage 2	10/01/2020	01/01/2020
2	B	Stage 3	20/01/2020	01/01/2020
3	C	Stage 1	10/01/2020	10/01/2020
3	C	Stage 3	25/01/2020	10/01/2020

The above is my data structure

I want to calculate the days between Stage 1 to stage 2 

For Example 

Lead 1 taken 2 days from Stage 1 to Stage 2

Lead 2 taken 10 days from Stage 1 to Stage 2

Lead 3 directly moved to Stage, So 0 days

12/3

  OutPut is 4 Days 

Like wise we have to find for Stage 2 to Stage 3

I don't have any idea whether we have to build the logic in Expression level or Loader level

Need your help

[rubenmarin · Accepted solution]

Hi, this can be done using a sorted load and peek() to access previous record data.

I attached a sample as an example, you can fix it to work as you expect to calculate stages.

[marishnagendran]

I'm using Qliksense

The file is not converted into Qvf while using Qliksense QMC.

Kindly Share the script with this thread.

[marishnagendran]

Now its working fine Ruben thanks.

[rubenmarin · Accepted solution]

Ops, sorry, I thought I was on QlikView forums

DataOrig:
LOAD * Inline [
Lead ID,	Cust,	Stage, 	lead Status Creation Date,	Lead Creation Date
1,	A,	Stage 1,	01/01/2020,	01/01/2020
1,	A,	Stage 2,	03/01/2020,	01/01/2020
1,	A,	Stage 3,	05/01/2020,	01/01/2020
2,	B,	Stage 1,	01/01/2020,	01/01/2020
2,	B,	Stage 2,	10/01/2020,	01/01/2020
2,	B,	Stage 3,	20/01/2020,	01/01/2020
3,	C,	Stage 1,	10/01/2020,	10/01/2020
3,	C,	Stage 3,	25/01/2020,	10/01/2020
];

DataWithDays:
LOAD
	[Lead ID],	
	Cust,	
	Stage, 	
	[lead Status Creation Date],	
	[Lead Creation Date],
	If(Stage='Stage 3' and Peek([Lead ID])=[Lead ID] and Peek(Stage)='Stage 1'
	  ,0
	  ,If(Stage='Stage 2' and Peek([Lead ID])=[Lead ID]
	    ,[lead Status Creation Date]-Peek([lead Status Creation Date])
	)) as DaysToStage2,
	If(Stage='Stage 3' and Peek(Stage)='Stage 2' and Peek([Lead ID])=[Lead ID] 
	  ,[lead Status Creation Date]-Peek([lead Status Creation Date])
	) as DaysToStage3
Resident
	DataOrig
;

DROP Table DataOrig;