Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bujnakbranislav
Partner
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.

16 Replies
vinieme12
Champion II
Champion II

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];

bujnakbranislav
Partner
Partner
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 II
Champion II

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

bujnakbranislav
Partner
Partner
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 II
Champion II

Glad to help

Cheers

V

bujnakbranislav
Partner
Partner
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 II
Champion II

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

View solution in original post