Calculate Difference Between Dates

[LP27]

Hi Everyone,

I want to figure out a way to calculate difference between dates columns below.

Here is my Data set below-

Number Id	Loop Number:	FOT Date	Loop Date	Status
160030-0006	TL1	7/26/2019	8/16/2019	Open
160030-0006	TL2	9/16/2019	10/16/2019	Open
160030-0006	TL3	10/26/2019	11/25/2019	Open
160030-0006	TL4	12/5/2019	1/4/2020	Closed
206699-4001	TL1	1/14/2020	2/13/2020	Open
206699-4001	TL2	2/15/2020	2/16/2020	Open
206699-4001	TL3	2/20/2020	2/22/2020	Closed

I want to calculate No of days between Loop Date and FOT Date for each Number Id when there Status are Open only.

Example: For a Number Id below, TL1 is the first loop so Count of Days will be dash, For TL2: the count of days is calculated by the logic

TL2= Loop Date of (TL1) - FOT Date of (TL2)

TL3=Loop date of(TL2) - FOT Date of (TL2)

Number Id	Loop Number:	FOT Date	Loop Date	Status	Count of  Days
206699-4001	TL1	1/14/2020	2/13/2020	Open	-
206699-4001	TL2	2/15/2020	2/16/2020	Open	2
206699-4001	TL3	2/20/2020	2/22/2020	Closed	4

I need the count of days like these:-

TL1

2 Days Difference

TL2

4 days Difference

TL3

Note :

1. For each Number ID, there will be Loop number from TL1 to TL5 some times it can extend till TL10 too.
2. I might also view this in a stacked Bar chart for visualization. 

Not sure how to proceed !

Please let me know if you have question.

Thanks in advance

[rwunderlich]

Load your raw data into a table, for example named  "Data". Then update the resident table like this:

Left Join(Data)
LOAD
  [Number Id],
  [Loop Number:],
  if([Number Id] = Previous([Number Id]),
    [FOT Date] - Previous([Loop Date]),
    '-') as Duration
Resident Data
Order By [Number Id], [Loop Number:];

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[LP27]

Thanks Rob !

But I tried the same scripts which you shared, its not working properly. 

kindly see the output below - 

Number ID	Loop Number:	FOT Date	Loop Date	Status	Duration
160030-0006	TL1	7/26/2019	8/16/2019	Open	21
160030-0006	TL2	9/16/2019	10/16/2019	Open	30
160030-0006	TL3	10/26/2019	11/25/2019	Open	30 10
160030-0006	TL4	12/5/2019	1/4/2020	Closed	30 10
206699-4001	TL1	1/14/2020	2/13/2020	Open	30
206699-4001	TL2	1/30/2019	3/11/2019	Open	-
206699-4001	TL3	2/25/2019	2/28/2019	Closed	-
348167004	TL1	1/18/2019	2/7/2019	Open	-
348167004	TL2	2/14/2019	2/17/2019	Open	-
348167005	TL3	2/20/2019	2/23/2019	Open	-
348167006	TL4	3/5/2019	3/8/2019	Closed	-

There are dashes in last 6 rows of Duration's Column and for the number id = 160030-0006 in above table, In loop number TL3, it should display Loop date(TL2) 10/16/2019 - FOT date (TL3) 10/26/2019 = 10 days 

logic for duration should be Loop Date(T1) - FOT Date(T2) ...so on!

Also for the first appearance of Number ID it should be empty or dash.

Please let me know if you need additional information.

Thanks Rob! 

[rwunderlich]

Can you please post your script?

-Rob