Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bujnakbranislav
Contributor

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
vinieme12
Esteemed Contributor II

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

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

16 Replies
galax_allu
Valued Contributor

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

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

bujnakbranislav
Contributor

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

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

bujnakbranislav
Contributor

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

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:

LOAD

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;

vinieme12
Esteemed Contributor II

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

Your load script will be as follows

TABLE1:

LOAD

ID,

StepID,

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

FROM

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

TABLE2:

LOAD

*,

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;

vinieme12
Esteemed Contributor II

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

Check your Order By statement

it should be by ID,StepID

You don't need CreatedDate in Orderby clause

vinieme12
Esteemed Contributor II

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

please see my updated post below,

Attaching APP for reference

flores_mig
New Contributor II

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

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:

LOAD

  *,

  Previous(CreateDate) as Previous_Date,

  Interval(CreateDate - Previous(CreateDate)) as elapsed_time

Resident Data;

Drop Table Data;

interval.JPG

Regards!

Mike

bujnakbranislav
Contributor

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

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:

LOAD

*

,

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

 

IDActivityIDStepIdCreateDateElapseTime
111-12016-01-01 00:05:00-
311-32016-01-01 00:06:00-
411-52016-01-02 00:06:00-
222-12016-05-11 00:05:00-
522-32016-05-11 00:10:00-
bujnakbranislav
Contributor

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

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:

LOAD

 

  StepId,

  Previous(CreateDate) as Previous_Date,

  Interval(CreateDate - Previous(CreateDate)) AS ElapseTime

Resident [InstanceSteps];

Results:

  

IDActivityIDStepIdCreateDatePrevious_DateElapseTime
111-12016-01-01 00:05:00--
311-32016-01-01 00:06:002016-05-11 00:05:00-131.00
411-52016-01-02 00:06:002016-01-01 00:06:001.00
222-12016-05-11 00:05:002016-01-01 00:05:00131.00
522-32016-05-11 00:10:002016-01-02 00:06:00130.00