Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser222
Creator
Creator

Difference beetweeb dates?

Hi,

I have a requirement in which i need to find out difference between two dates excluding weekends.

Can any one please help me?

Please find attached which has raw data.

Thanks

1 Solution

Accepted Solutions
swuehl
MVP
MVP

And if you want to calculate duration between timestamps excluding weekends, have a look at

Calculate hours between two Date/Time strings

Then your script could look like

SET TimestampFormat='M/D/YYYY  h:mm:ss[.fff] TT';

Test:

load *,

NetWorkDays(date(startdate,'M/D/YYYY h:mm:ss'), date(enddate,'M/D/YYYY h:mm:ss')) as diffNetWorkDays,

Recno() as RecID

inline [

ID, Name, startdate, enddate

202, REM, 1/27/2016  2:34:32 PM,1/29/2016  4:47:57 PM

202, WAQ, 1/27/2016  2:34:33 PM, 2/1/2016  3:58:17 PM

303, XXX, 1/27/2016  2:34:35 PM, 2/3/2016  2:35:00 PM

303,YYY,2/23/2016  7:20:56 PM,3/24/2016  2:08:02 PM

404,XXX,1/5/2016  4:19:58 PM,1/6/2016  10:15:46 AM];

Set vHol = '41130,41140'; // just for demo optional holidays

TMP:

LOAD  RecID,

daystart(startdate)+iterno()-1 as Date,

if(iterno()=1, frac(startdate), 0) as Start,

if(daystart(startdate)+iterno()-1=daystart(enddate), frac(enddate),1) as End

Resident Test

while daystart(enddate) >= daystart(startdate)+iterno()-1;

left join (Test) LOAD

RecID,

sum(End-Start) as DurationHours

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by RecID;

drop table TMP;

ID Name startdate enddate diffNetWorkDays Interval(Sum(DurationHours),'h:mm:ss')
784:20:28
202REM1/27/2016 2:34:32 PM1/29/2016 4:47:57 PM350:13:25
202WAQ1/27/2016 2:34:33 PM2/1/2016 3:58:17 PM473:23:44
303XXX1/27/2016 2:34:35 PM2/3/2016 2:35:00 PM6120:00:25
303YYY2/23/2016 7:20:56 PM3/24/2016 2:08:02 PM23522:47:06
404XXX1/5/2016 4:19:58 PM1/6/2016 10:15:46 AM217:55:48

Hope this helps,

Stefan

View solution in original post

8 Replies
robert_mika
Master III
Master III

Interval((floor(timestamp(timestamp#(textbetween(enddate,'',' '),'MM/DD/YYYY'),'MM/DD/YYYY'))+

time(time#(textbetween(enddate,'  ', ''),'h:mm:ss tt'),'h:mm:ss'))

-(floor(timestamp(timestamp#(textbetween(startdate,'',' '),'MM/DD/YYYY'),'MM/DD/YYYY'))+

time(time#(textbetween(startdate,'  ', ''),'h:mm:ss tt'),'h:mm:ss')),'hh:mm:ss')   as diff

Feeling Qlikngry?

How To /Missing Manual(25 articles)

swuehl
MVP
MVP

Just use this statement to define your timestamp format:

SET TimestampFormat = 'M/D/YYYY  h:mm:ss TT';

Note the extra space between date and time part.

ecolomer
Master II
Master II

Probably your problem are date format for startdate and enddate

qlikuser222
Creator
Creator
Author

Hi,

Thanks for helpfull soulutions.

I am still unable to get the expected results.

I dont understand why id 202 is repeating 4 times, where as in script its only mentioned twice.

can anyone please explain where it went wrong??

swuehl
MVP
MVP

That's because the key between the two tables is only ID (Name has been renamed to NAME).

The Difference table still holds two records for  ID 202, hence since the Name key is missing, you get both differences per ID.

Create a primary key made from ID and Name in both tables or just use e.g. a preceding load with the diff code in the first load to overcome this issue.

Regarding your original request, I believe the code from your last answer does not exclude weekends.

And do you need the difference between timestamps or just the date difference? If latter is sufficient, you should be able to get what you want using NetWorkDays() function (that's what you also used in your sample QVW).

Just take care to interprete the timestamps correctly when reading in.

Regards,

Stefan

ehilsinger
Contributor III
Contributor III

Try the below script.

You have two spaces between the date and time.  I also used date# first.

Test:

load * inline [

ID, Name, startdate, enddate

202, REM, 1/27/2016  2:34:32 PM,1/29/2016  4:47:57 PM

202, WAQ, 1/27/2016  2:34:33 PM, 2/1/2016  3:58:17 PM

303, XXX, 1/27/2016  2:34:35 PM, 2/3/2016  2:35:00 PM

303,YYY,2/23/2016  7:20:56 PM,3/24/2016  2:08:02 PM

404,XXX,1/5/2016  4:19:58 PM,1/6/2016  10:15:46 AM];

Difference:

NoConcatenate

load ID,

Name as NAME,

startdate,

enddate,

Date (date#(startdate,'MM/DD/YYYY  hh:mm:ss TT')) as newstartdate,

Date(date#(enddate,'MM/DD/YYYY  hh:mm:ss TT')) as newenddate,

NetWorkDays(Date (date#(startdate,'MM/DD/YYYY  hh:mm:ss TT')), Date(date#(enddate,'MM/DD/YYYY  hh:mm:ss TT'))) as diff

Resident Test;

DROP Table Test;

maxgro
MVP
MVP

try the attachment

1.png

swuehl
MVP
MVP

And if you want to calculate duration between timestamps excluding weekends, have a look at

Calculate hours between two Date/Time strings

Then your script could look like

SET TimestampFormat='M/D/YYYY  h:mm:ss[.fff] TT';

Test:

load *,

NetWorkDays(date(startdate,'M/D/YYYY h:mm:ss'), date(enddate,'M/D/YYYY h:mm:ss')) as diffNetWorkDays,

Recno() as RecID

inline [

ID, Name, startdate, enddate

202, REM, 1/27/2016  2:34:32 PM,1/29/2016  4:47:57 PM

202, WAQ, 1/27/2016  2:34:33 PM, 2/1/2016  3:58:17 PM

303, XXX, 1/27/2016  2:34:35 PM, 2/3/2016  2:35:00 PM

303,YYY,2/23/2016  7:20:56 PM,3/24/2016  2:08:02 PM

404,XXX,1/5/2016  4:19:58 PM,1/6/2016  10:15:46 AM];

Set vHol = '41130,41140'; // just for demo optional holidays

TMP:

LOAD  RecID,

daystart(startdate)+iterno()-1 as Date,

if(iterno()=1, frac(startdate), 0) as Start,

if(daystart(startdate)+iterno()-1=daystart(enddate), frac(enddate),1) as End

Resident Test

while daystart(enddate) >= daystart(startdate)+iterno()-1;

left join (Test) LOAD

RecID,

sum(End-Start) as DurationHours

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by RecID;

drop table TMP;

ID Name startdate enddate diffNetWorkDays Interval(Sum(DurationHours),'h:mm:ss')
784:20:28
202REM1/27/2016 2:34:32 PM1/29/2016 4:47:57 PM350:13:25
202WAQ1/27/2016 2:34:33 PM2/1/2016 3:58:17 PM473:23:44
303XXX1/27/2016 2:34:35 PM2/3/2016 2:35:00 PM6120:00:25
303YYY2/23/2016 7:20:56 PM3/24/2016 2:08:02 PM23522:47:06
404XXX1/5/2016 4:19:58 PM1/6/2016 10:15:46 AM217:55:48

Hope this helps,

Stefan