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'

[#2]

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

[stevedark]

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

[stevedark]

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

[stevedark]

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

[#6]

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

[stevedark]

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