Qlik Community

New to Qlik Sense

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

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DB6000
Contributor
Contributor

Calculating interval between startdate/time and enddate/time

Hi All,

Could anyone assist me with getting  the interval time between jobs with a start date and time and end date and time?

I have a custom format dd/mm/yyyy hh:mm

for example:

jobidStartTimeEndTime
401/02/2020 09:5601/02/2020 10:08
901/02/2020 10:4801/02/2020 10:56
1301/02/2020 10:5901/02/2020 11:12
1801/02/2020 11:1401/02/2020 11:23

 

I tried this but it didn't work

Interval(StartTime - EndTime, 'hh:mm')

Thanks in advance!

 

 

3 Replies
QFabian
Specialist II
Specialist II

Hi @DB6000 , you can try changing this, from 'DD-MM-YYYY' to 'DD/MM/YYYY' :

QFabian_0-1633368610919.png

or replacing it in the field  '-' TO '/'

replace(yourdate, '-', '/') as your date.

 

plese let me know if it works for you.

QFabian
DB6000
Contributor
Contributor
Author

Hi @QFabian 

Thank you for the prompt reply, appreciate your help!  I changed the date format and I still don't see the correct results.  I am creating a chart (Bar chart) to show  elapsed time in hh:mm from start date/time to enddate/time. basically the runtime of the job and how long it took.  I don't have a separate column for job runtime (i may need to do this calculation), hoping i could script to figure out elapsed time/date between jobs.

jobid

StartTime

EndTime

Locations

4

01/02/2020 09:56

01/02/2020 10:08

9

9

01/02/2020 10:48

01/02/2020 10:56

8

206

01/03/2020 11:00

01/03/2020 11:15

81

212

01/03/2020 11:07

01/03/2020 11:54

2

386

01/06/2020 16:50

01/06/2020 16:54

2

387

01/06/2020 16:51

01/06/2020 16:58

22

942

01/09/2020 21:26

01/09/2020 22:13

486

943

01/09/2020 21:26

01/09/2020 21:34

10

 

a chart something like this attached file. showing number of jobs runtime per month/locations

 

Screenshot 2021-10-05 at 12.00.33.png

 

 

 

 

 

 

 

 

 

 

 

QFabian
Specialist II
Specialist II

HI @DB6000 , here i worte an option for you :

 

QFabian_0-1633561839174.png

 

Here the script, notice my variables, i didnt touch them :

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='$#.##0;$-#.##0';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='es-CL';
SET CreateSearchIndexOnReload=1;
SET MonthNames='ene.;feb.;mar.;abr.;may.;jun.;jul.;ago.;sep.;oct.;nov.;dic.';
SET LongMonthNames='enero;febrero;marzo;abril;mayo;junio;julio;agosto;septiembre;octubre;noviembre;diciembre';
SET DayNames='lun.;mar.;mié.;jue.;vie.;sáb.;dom.';
SET LongDayNames='lunes;martes;miércoles;jueves;viernes;sábado;domingo';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Data_Aux:
Load * INLINE [
jobid, StartTime, EndTime, Locations
206,01/03/2020 11:00, 01/03/2020 11:15, 81
212,01/03/2020 11:07, 01/03/2020 11:54, 2
386,01/06/2020 16:50, 01/06/2020 16:54, 2
387,01/06/2020 16:51, 01/06/2020 16:58, 22
942,01/09/2020 21:26, 01/09/2020 22:13, 486
943,01/09/2020 21:26, 01/09/2020 21:34, 10
4,01/02/2020 09:56, 01/02/2020 10:08, 9
9,01/02/2020 10:48, 01/02/2020 10:56, 8
];

Data:
Load
*,
time((EndTime - StartTime)) as ElapsedTime;
Load
jobid as JobId,
date#(StartTime, 'DD/MM/YYYY hh:mm') as StartTime,
date#(EndTime, 'DD/MM/YYYY hh:mm') as EndTime,
Locations
Resident Data_Aux;
drop table Data_Aux;

 

Hope works for you!

QFabian