topic Re: If statement in Interval count in QlikView

If statement in Interval count

Sun, 03 Apr 2016 06:55:14 GMT

Hi,

How can I add if statement when calculating interval between two dates?

I want to calculate number of day from OrderDate to DeliveryDate.

I am using the following simple expression as both fields are timestamps.

= Date(DeliveryDate) - Date(OrderDate)

but if Order was placed after 12PM then I want to - 1 from the Total.

Thanks

Re: If statement in Interval count

Kushal_Chawda — Sun, 03 Apr 2016 07:50:40 GMT

may be this will be helpful

Working hours only

Re: If statement in Interval count

HirisH_V7 — Sun, 03 Apr 2016 09:09:28 GMT

Hi,

Check this,

From a sample data,

Data:
LOAD *,
Interval([Delivery Date]-[Order Date],'DD') as Days,
Interval(IF(TimeStamp#(TimeStamp([Order Date],'h:mm:ss[.fff] TT'),'h:mm:ss[.fff] TT')>='12:00:00 PM',
  (Interval([Delivery Date]-[Order Date],'DD'))-1,
  Interval([Delivery Date]-[Order Date],'DD')) ,'DD')as DaysWithCondition
INLINE [
Delivery Date,Order Date
02/04/2015 11:23:07 AM,30/03/2015 11:23:09 AM
02/04/2015 11:23:07 AM,30/03/2015 12:00:00 PM
02/04/2015 11:23:07 AM,30/03/2015 01:00:00 PM
];

By using the if Condition For time >='12:00:00 PM' and Interval Function calculating the number of days Difference,

Here is the Difference at output,

IF we use timestamp means we can get the accurate time lapse between two dates, For now its only Days Difference,

Hope this helps,

PFA,

Hirish

Re: If statement in Interval count

Sun, 03 Apr 2016 09:14:15 GMT

Thank Harish,

Is it possible to exclude weekend and public holidays?

I have a separate table listing all the public holiday dates. would that help ?

Re: If statement in Interval count

HirisH_V7 — Sun, 03 Apr 2016 10:22:55 GMT

Please provide me a sample data

Re: If statement in Interval count

sunny_talwar — Sun, 03 Apr 2016 10:26:57 GMT

If you want to exclude weekends and public holidays from the difference of order date and delivery date, you can use NetWorkDays() function.

Find Net Working Days

Re: If statement in Interval count

Sun, 03 Apr 2016 10:46:47 GMT

Thank Sunny.. I did post a similar post couple of days ago but no one replied. Do you think the link you suggested will help me solve the question I posted here.. how to calculate days between two timestamps Thanks