Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reference Time in If Statements

Hi,

I am trying to find out the number of days it takes to ship orders - but any order placed after 4 pm cannot be shipped the same day and therefore should not have to count the actual CompletedDate as a day.

In essence, if an order that is placed at noon, and ships the same day should have 0 days to ship, but an order placed that same day at 8 pm, then ships the following day should also show 0 days.

I am trying to use an If Statement, so that if the order is placed past 4 pm, then it takes one day off of the shipping days.

Example:

CompletedTimeCompletedDateShipDate
8:00:00 AM1/1/20151/1/2015
5:00:00 PM1/1/20151/2/2015
12:00:00 PM1/1/20151/5/2015

The first two entries should both have 0 days to ship - while the last one should have 5.

My problem is referencing time in my If Statement - it doesn't want to recognize the various formats I am trying to use.

IF(CompletedTime>='4:00:00 PM',(Date(ShipDate)-Date(CompletedDate) -1), Date(ShipDate)-Date(CompletedDate))

It keeps telling me all my orders are after 4 pm, when they aren't. (I tested this by doing an If statement like above, only where the then condition just told me "After 4" instead of doing the equation.)

1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Andrea,

You need to keep in mind that with a data, if it's stored correctly, that a day is actually a value of 1, and a time is a decimal portion of that, e.g. midday is .5.

Given this, you can work out the time to compare to with a sum:

if(CompletedTime >= (1/24*16),

This assumes that the CompletedTime field only has the time in, and not date time.  If it is date time the code becomes:

if((CompletedTime - DayStart(CompletedTime)) >= (1/24*16),


Here you are effectively removing all the days from the time value (i.e. up to midnight at the start of the day) leaving only the decimal portion.

I hope that all makes sense?

Steve

View solution in original post

4 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Andrea,

You need to keep in mind that with a data, if it's stored correctly, that a day is actually a value of 1, and a time is a decimal portion of that, e.g. midday is .5.

Given this, you can work out the time to compare to with a sum:

if(CompletedTime >= (1/24*16),

This assumes that the CompletedTime field only has the time in, and not date time.  If it is date time the code becomes:

if((CompletedTime - DayStart(CompletedTime)) >= (1/24*16),


Here you are effectively removing all the days from the time value (i.e. up to midnight at the start of the day) leaving only the decimal portion.

I hope that all makes sense?

Steve

Not applicable
Author

Hi, that worked! And thank you for adding the part with a date and timestamp - since I did have a date in there as well.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

That was just a lucky guess!

Glad it's working now.

MarcoWedel

Hi,

I was just about to post a (wrong) solution suggesting that you might be comparing strings rather than numbers due to a non-matching TimeFormat variable, when I found your thread already answered correctly.

Just some thoughts to supplement the solution:


CompletedTime - DayStart(CompletedTime)

might as well be calculated as


Frac(CompletedTime)


which extracts the (fractional) time part from a timestamp value.





Date(ShipDate)-Date(CompletedDate)


should be the same as


ShipDate-CompletedDate


since the formatting Date()-function does not change the numerical value of a date/timestamp,



hope this helps


regards


Marco