how to calculate days between two timestamps

[#1]

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'

[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.

[#3]

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 · Accepted solution]

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.

[#7]

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.

[#8]

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.

[#9]

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

[#10]

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?