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: 
Not applicable

how to calculate days between two timestamps

Hi guys,

What am I trying to achieve?

I want to calculate following two types of total days between two timestamp fields.

  1. Number of Working Days (Excluding Weekend & Public Holidays)
  2. Number of Total Days (Including Weekend & Public Holidays)

Calculation Condition

  • If OrderDate time is <= 12:00 PM then start count from 0
  • If OrderDate Time is > 12:00 PM then start count from -1

Count day from Order Date till Delivery Date but

  • if Delivery Date is NULL then count till Today's date


Data Model

  • [OrderDate] & [DeliveryDate] resides in same 'OrderTable'
  • [PublicHolidayDate] resides in separate 'PublicHolidaysTable'
1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Lokesh,

In this case the bank holidays needs to be a comma separated list of bank holiday dates, like this:

NetWorkDays(OrderDate, Alt(DeliveryDate, Today()), '01/01/2016','25/03/2016','28/03/2016')


It would be best to get the bank holidays from a file, rather than have to key them in, so you would want something like:

Temp_Hols:

LOAD

  Holiday

FROM HolidayDates.xls

(...format specifier etc...);

let vHols = '';

for iHol = 0 to NoOfRows('Temp_Hols') -1

    let vHols = vHols & if(iHol > 0, ',', '') & chr(39) & Date(peek('Holiday', iHol, 'Temp_Hols')) & chr(39);

next

DROP TABLE Temp_Hols;

LOAD

  OrderDate,

  DeliveryDate,

  NetWorkDays(OrderDate, Alt(DeliveryDate, Today()), $(vHols)) as [Working Days],

  Alt(DeliveryDate, Today()) - OrderDate as [Total Days],

FROM ...

I've just typed this code directly into the thread, so there are likely to be syntax errors - but it should point you in the right direction.

Cheers,
Steve

View solution in original post

15 Replies
sunny_talwar

Can you share few rows of data with expected output? Usually the best possible way to get help is to provide these two things (+ the logic, if the logic for output in not straight forward) and you will be amazed how quickly you get responses.

Not applicable
Author

HI Sunny

Thank you for the advice, I will follow your instructions in my future posts

I have uploaded a sample QVD in the original discussion of this post.. . Your time and help will be highly appreciated.

Thanks

rubenmarin

Hi Lokesh, you can try something like:

NetWorkDays(OrderDate, Alt(DeliveryDate, Today()), PublicHolidayDate)

+ If(Frac(Time(OrderDate))>0.5, -1, 0)

In the sample provided the OrderDate is not really a Date, it's a string and it should be a Date. And also the PublicHolidayDate doesn't exists--> It's is 31/04 and april has 30 days.

Also the only Order with DeliveryDate has it's DeliveryDate lower than OrderDate.

Hope it works with real data.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Lokesh,

In this case the bank holidays needs to be a comma separated list of bank holiday dates, like this:

NetWorkDays(OrderDate, Alt(DeliveryDate, Today()), '01/01/2016','25/03/2016','28/03/2016')


It would be best to get the bank holidays from a file, rather than have to key them in, so you would want something like:

Temp_Hols:

LOAD

  Holiday

FROM HolidayDates.xls

(...format specifier etc...);

let vHols = '';

for iHol = 0 to NoOfRows('Temp_Hols') -1

    let vHols = vHols & if(iHol > 0, ',', '') & chr(39) & Date(peek('Holiday', iHol, 'Temp_Hols')) & chr(39);

next

DROP TABLE Temp_Hols;

LOAD

  OrderDate,

  DeliveryDate,

  NetWorkDays(OrderDate, Alt(DeliveryDate, Today()), $(vHols)) as [Working Days],

  Alt(DeliveryDate, Today()) - OrderDate as [Total Days],

FROM ...

I've just typed this code directly into the thread, so there are likely to be syntax errors - but it should point you in the right direction.

Cheers,
Steve

rubenmarin

Hi, thanks for correcting me Steve, I never have to use that function. Good to know if one day I find the need.

I was thinking in a chart solution, in that case I update my previous expression to:

NetWorkDays(OrderDate_Date, Alt(DeliveryDate, Today()), '$(=Concat(PublicHolidayDate, Chr(39) & ',' & Chr(39)))')

...But it can be improved creating the variable in script as Steve says and then use it in the expression:

NetWorkDays(OrderDate_Date, Alt(DeliveryDate, Today()), $(vHols))

...But the option to have the values calculated in the script proposed by Steve is the best choice.

Not applicable
Author

Hi Stevan,

Thank you very much for your reply and sorry for messed up sample QVW..

I have tried your Technic but it is not giving me desired result. It is giving me one extra day in the resut.

I have attached a zip file with another sample QVW with sample QVD file with expected result and it would be great if you can have a look on holiday variable i created.

Thanks.

Not applicable
Author

Thanks Ruben,

I have adjusted ( + / - )  the dates in SQL before creating QVD to make it simpler..

I have attached another ZIP file with better sample QVW and QVD thanks.

Not applicable
Author

Hi Lokesh

One suggestion -

If your using SQL to generate QVD then do all this calculation SQL itself.

SQL having standard functions available for this, and it will not increase any load on your qvd.Data processing will be faster if you handle in script itself.

Regards,

Lokesh

Not applicable
Author

Thanks for the tip Lokesh S,

Wouldn't it make SQL query slow ? when performing incremental load?

and what i think SQL query will be more complex then qlikview?