# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for
Did you mean:
Contributor

## Date Calculations within same field

Hello,

I need help with a load script.  I'm trying to calculate the difference between multiple time records with different Event IDs, for each PersonID.  I have a table like below.  EventID 1 = Start Time, EventID2 = End Time.  There can be multiple Start and End Times per date.

 PersonID DateTime EventID 1 1/1/19 7:05 AM 1 2 1/1/19 8:03 AM 1 1 1/1/19 12:00 PM 2 1 1/1/19 1:00 PM 1 2 1/1/19 4:00 PM 2 1 1/1/19 4:30 PM 2

I need to find the total duration between the EventIDs 1 and 2 for each Person ID and Date like:

 PersonID Date Duration 1 1/1/2019 8:25:00 2 1/1/2019 7:57:00

I think I need to use some combination of Previous and Peek, but not sure exactly how to accomplish this.  Any help pointing me in the right direction would be appreciated.

Edit: Corrected dates in example table

1 Solution

Accepted Solutions
Specialist III

If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference

Channa
6 Replies
Specialist II

Something like this:

```LOAD
PersonID,
Date(Floor(DateTime)) AS Date,
if(EventID=2, if(Previsou(PersonID)=PersonID and Previous(EventID)=1,Previous(DateTime) - DateTime)) as Duration
;
Select
PersonID,
DateTime,
EventID
From (yourtable)
Order By PersonID, DateTime, EventID;```
Specialist III

how ru calculating Duration 8:25

Channa
Contributor
Author

I'm sorry, my example had error, all dates should have been 1/1/2019.  I have edited original post, please see example table again.  It is the total duration for the day 1/1/2019

7:05AM to 12:00PM = 4:55

1:00PM to 4:30PM = 3:30

4:55 + 3:30 = total duration of 8:25

Specialist III

If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference

Channa
Contributor
Author

Thanks, I got it to work with a combination of both of your responses.

However, I have found that there is an additional issue with my data that I need to solve for.  Sometimes the Start and End Times happen at the same second, so when I do the order by it doesn't always sort the way I need it to, with Start Time before End Time.

How can I either make sure the sort always has Start Time before End Time or just exclude those events that happen simultaneously?

Thanks

Contributor
Author

I figured it out, just have to sort by DateTime Asc, EventID Desc.

Thanks!