Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pentaxadmin
Partner - Creator
Partner - Creator

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.

17 Replies
vinieme12
Champion III
Champion III

You should be using ActivityID not ID I think Id here is just rownumber

ActivityID and StepId are related to each other not ID

USING YOUR SCRIPT __CHANGES IN BOLD

[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:

LOAD

*

,

// StepId,

IF(ActivityID = Previous(ActivityID),

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 ActivityID,StepId;

DROP TABLE [InstanceSteps];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pentaxadmin
Partner - Creator
Partner - Creator
Author

It is not assigning correct previous date. Previous date should be only associated within the same activityID

So, for ActivityID = 1 I have 3 steps and for ActivityID = 2 I have 2 steps. I need to calculate lapse time between the steps for each activity

vinieme12
Champion III
Champion III

change to below

IF(ActivityID = Previous(ActivityID),

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pentaxadmin
Partner - Creator
Partner - Creator
Author

Thanks Pujari,

It looks like the first code was working with slight delta when I did compare in excel, maybe rounding issue, but the change you sent me works great so far. will let you know once I plug it into my model if I am getting the same results

     

IDActivityIDStepIdCreateDateElapseTimecheckdelta
222-12016-05-11 00:05:00 -
522-32016-05-11 00:10:00         0.0034722222263 0.003472222                                 -  
111-12016-01-01 00:05:00 -
311-32016-01-01 00:06:00         0.0006944444467 0.000694444                                 -  
411-52016-01-01 01:06:00         0.0416666666642 0.0416666667                                 -  
                                 -  
vinieme12
Champion III
Champion III

Glad to help

Cheers

V

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
pentaxadmin
Partner - Creator
Partner - Creator
Author

Thanks. I got it working, I am having some issues with some calculations, but I believe that's due to the date formats.

Thank you again for your help. !!!

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
AshwinDaniel
Contributor III
Contributor III

Dear Connections,

What function do I need to use for sum up all Elaspse Time? as a KPI.

Regards,

Ashwin Daniel