topic Re: how to calculate days between two timestamps in QlikView

how to calculate days between two timestamps

Sat, 02 Apr 2016 03:42:38 GMT

Hi guys,

What am I trying to achieve?

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

Number of Working Days (Excluding Weekend & Public Holidays)
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'

Re: how to calculate days between two timestamps

sunny_talwar — Sun, 03 Apr 2016 10:49:41 GMT

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.

Re: how to calculate days between two timestamps

Sun, 03 Apr 2016 11:28:18 GMT

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

Re: how to calculate days between two timestamps

rubenmarin — Tue, 05 Apr 2016 17:00:06 GMT

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.

Re: how to calculate days between two timestamps

stevedark — Tue, 05 Apr 2016 17:16:36 GMT

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

Re: how to calculate days between two timestamps

rubenmarin — Tue, 05 Apr 2016 18:18:22 GMT

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.

Re: how to calculate days between two timestamps

Wed, 06 Apr 2016 05:57:36 GMT

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.

Re: how to calculate days between two timestamps

Wed, 06 Apr 2016 06:00:13 GMT

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.

Re: how to calculate days between two timestamps

Wed, 06 Apr 2016 06:51:20 GMT

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

Re: how to calculate days between two timestamps

Wed, 06 Apr 2016 07:01:29 GMT

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?

Re: how to calculate days between two timestamps

Wed, 06 Apr 2016 07:22:00 GMT

Hi Lokesh

As per your requirement SQL work faster.

In SQL you can use group by, ROLLUP and one month parameter.

I guess SQL will take some fraction of seconds to retrieve your required data.

In Qlikview if you add those calculation in expressions then it will get slower as time goes on because of data load incremental.

This i observed in my daily practice.

Regards,

Lokesh

Re: how to calculate days between two timestamps

stevedark — Wed, 06 Apr 2016 07:26:30 GMT

Hi Lokesh,

Dates in QlikView (as in Excel and SQL etc.) are simply numbers that look a bit different. A whole day is an integer, i.e. 1.

If you are consistently one day out then you can simply do a -1 to fix it up.

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

Hope that helps.

Steve

Re: how to calculate days between two timestamps

stevedark — Wed, 06 Apr 2016 07:34:58 GMT

Hi,

I'm not sure if SQL has a function as flexible as the QlikView NetworkDays function?

This is not going to be a heavy calculation, so it doesn't matter too much where it is done from that point of view - but personally I would always tend to do it on the QlikView side.

As you mention if you have a decent incremental load into QVDs then it only needs to be re-calculated when any given row changes.

Steve

Re: how to calculate days between two timestamps

stevedark — Wed, 06 Apr 2016 07:37:36 GMT

Note also... I didn't include the midday calculation, as you could take that from RubenMarin‌ 's solution above:

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

It may be that you require this?

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

+ If(Frac(Time(OrderDate))>0.5, -1, 0) as [Working Days],

Steve

Re: how to calculate days between two timestamps

Wed, 06 Apr 2016 07:59:05 GMT

Hi Steve,

Thanks for advice.. The order table QVD gets updated every 30 minutes with modified records and on average its around 250 records each incremental load.

I have attached a QVW and QVD in a zip file with your Holiday variable formula but its varying by one extra day from desired outcome.

Would you please have a look on my QVW if you get a change?

Thanks

Re: how to calculate days between two timestamps

stevedark — Wed, 06 Apr 2016 17:14:20 GMT

Can you not just -1 at the end of the expression? NetworkDays includes both the start and end days, and it sounds like you need to count only one end or the other?

Steve