Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

If statement in Interval count

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

6 Replies

Re: If statement in Interval count

may be this will be helpful

Working hours only

HirisH_V7
Honored Contributor

Re: If statement in Interval count

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,

Date(DeliveryDate) - Date(OrderDate).PNG

Hope this helps,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Not applicable

Re: If statement in Interval count

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 ?

HirisH_V7
Honored Contributor

Re: If statement in Interval count

Please provide me a sample data

HirisH
“Aspire to Inspire before we Expire!”

Re: If statement in Interval count

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

Not applicable

Re: If statement in Interval count

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