# New to Qlik Sense

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

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for
Did you mean:
Partner

## How to calculate elapse time between two dates in the same field

Dear Community:

I have a challenge to calculate elapse time between different time stamps of steps in activity.

ID, Activity ID, Step ID, CreateDate,

1, 1, 1-1, 1/1/16 05:00:10,

2, 1, 1-3, 1/1/2016 05:05:00,

3, 1, 1-5, 1/1/2016 06:10:00

I am trying to create ElapseTime Fields that will calculate the time difference between the timestamps in the same activity

My result field should look like the following

ID, ID, Activity ID, Step ID, CreateDate, ElapseTime

1, 1, 1-1, 1/1/16 05:00:10, 0

2, 1, 1-3, 1/1/2016 05:05:00, 00:04:50

3, 1, 1-5, 1/1/2016 06:10:00, 01:05:00

Thanks for your help once again.

1 Solution

Accepted Solutions
Champion II

Can you mark a post as correct and close this thread please.

16 Replies
Specialist

Hi

not tested , try this

Interval(Timestamp#(CreateDate,'DD/MM/YYYY hh:mm:ss')- Timestamp#(previous(CreateDate),'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss')    AS ElapseTime

Partner
Author

It works, but once I have multiple records it seems not to work properly

I need to make sure that timelaps are calculated for each activity between the steps

Partner
Author

I am getting -

[InstanceSteps]:

LOAD * INLINE [

ID, ActivityID, StepId, CreateDate,

1, 1, 1-1, 2016-01-01 00:05:00,

2, 2, 2-1, 2016-05-11 00:05:00,

3, 1, 1-3, 2016-01-01 00:06:00,

4, 1, 1-5, 2016-01-02 00:06:00,

5, 2, 2-3, 2016-05-11 00:10:00

];

TimeLapsSteps:

StepId,

IF(StepId= previous(StepId),

Interval(Timestamp#(CreateDate,'YYYY-MM-DD hh:mm:ss')- Timestamp#(previous(CreateDate),'YYYY-MM-DD hh:mm:ss'), 'hh:mm:ss'))    AS ElapseTime

resident [InstanceSteps] Order by CreateDate,StepId;

Champion II

Your load script will be as follows

TABLE1:

ID,

StepID,

DATE(Dateval,'DD/MM/YYYY hh:mm:ss') as CreateDate

FROM

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

TABLE2:

*,

if(ID = Previous(ID), time(rangesum(Interval(Timestamp#(CreateDate,'DD/MM/YYYY hh:mm:ss')- Timestamp#(Previous(CreateDate),'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss'),peek(ElapseTime))),null() )    AS ElapseTime

RESIDENT TABLE1

ORDER BY ID,StepID;

Drop Table TABLE1;

Champion II

Check your Order By statement

it should be by ID,StepID

You don't need CreatedDate in Orderby clause

Champion II

please see my updated post below,

Attaching APP for reference

Partner

Hi Branislav.

Hope this helps....

Data:

LOAD * INLINE [

ID,  Activity ID,  Step ID,  CreateDate

1,1,1-1,01/01/2016 05:00:10 AM

2,1,1-3,01/01/2016 05:05:00 AM

3,1,1-5,01/01/2016 06:10:00 AM

];

Calc_prev_date:

*,

Previous(CreateDate) as Previous_Date,

Interval(CreateDate - Previous(CreateDate)) as elapsed_time

Resident Data;

Drop Table Data;

Regards!

Mike

Partner
Author

It's still doesn't work. I can not see your attached example, but here is the script and result

[InstanceSteps]:

LOAD * INLINE [

ID, ActivityID, StepId, CreateDate,

1, 1, 1-1, 2016-01-01 00:05:00,

2, 2, 2-1, 2016-05-11 00:05:00,

3, 1, 1-3, 2016-01-01 00:06:00,

4, 1, 1-5, 2016-01-02 00:06:00,

5, 2, 2-3, 2016-05-11 00:10:00

];

NOCONCATENATE

TimeLapsSteps:

*

,

// StepId,

IF(ID = Previous(ID),

time(rangesum(Interval(Timestamp#(CreateDate,'YYYY-MM-DD hh:mm:ss')- Timestamp#(previous(CreateDate),'YYYY-MM-DD hh:mm:ss'), 'hh:mm:ss'),peek(ElapseTime))),null() )    AS ElapseTime

resident [InstanceSteps] Order by ID,StepId;

DROP TABLE [InstanceSteps];

Result:

 ID ActivityID StepId CreateDate ElapseTime 1 1 1-1 2016-01-01 00:05:00 - 3 1 1-3 2016-01-01 00:06:00 - 4 1 1-5 2016-01-02 00:06:00 - 2 2 2-1 2016-05-11 00:05:00 - 5 2 2-3 2016-05-11 00:10:00 -
Partner
Author

Does not work: See my script below and results:

[InstanceSteps]:

LOAD * INLINE [

ID, ActivityID, StepId, CreateDate,

1, 1, 1-1, 2016-01-01 00:05:00,

2, 2, 2-1, 2016-05-11 00:05:00,

3, 1, 1-3, 2016-01-01 00:06:00,

4, 1, 1-5, 2016-01-02 00:06:00,

5, 2, 2-3, 2016-05-11 00:10:00

];

Calc_prev_date:

StepId,

Previous(CreateDate) as Previous_Date,

Interval(CreateDate - Previous(CreateDate)) AS ElapseTime

Resident [InstanceSteps];

Results:

 ID ActivityID StepId CreateDate Previous_Date ElapseTime 1 1 1-1 2016-01-01 00:05:00 - - 3 1 1-3 2016-01-01 00:06:00 2016-05-11 00:05:00 -131.00 4 1 1-5 2016-01-02 00:06:00 2016-01-01 00:06:00 1.00 2 2 2-1 2016-05-11 00:05:00 2016-01-01 00:05:00 131.00 5 2 2-3 2016-05-11 00:10:00 2016-01-02 00:06:00 130.00
Tags
Community Browser