topic Conditions based on time stamps in QlikView

Conditions based on time stamps

Tue, 30 Aug 2011 18:27:11 GMT

I have 2 columns

1 - releasedate(without time stamp)

2- Shiptime ( date with time stamp)

I have to have a condition in my data such that ...i need to count the id's based on shiptime and release date

If the release is on say Aug 30 , and ship time is one day before aug 30, that is aug 29th and after 1:00pm , then put it in 'with in 1 day',if ship time is one or more days before 1 pm , then put it in '1 day or more'

How do i write this condition for a pie chart ?

I am bit confused ..

Re: Conditions based on time stamps

swuehl — Tue, 30 Aug 2011 22:33:30 GMT

If I understood your problem correctly, I would use something like this as flag in the script or as calculated dimension:

if( (ReleaseDate - ShipTimestamp) <= 11/24, 'within 1 day ','1 day or more ')

Dates and Timestamps are numericals in principal, so you can just subtract two dates / timestamps (a timestamp is a date with decimal places). 11/24 is indicating 11 hours, which is the difference between midnight and 1:00 pm the day before, right?

(I noticed though that a Shiptimestamp of exactely 1 pm on Aug 29th and a release date of 30 Aug lead to 1 day or more, one second later and it fell within 1 day).

Conditions based on time stamps

Wed, 31 Aug 2011 04:25:53 GMT

This gives me correct answers , thank you very very much , you made my expression so simple , i have been trying multiple ways with if statements in the scripts.

Could you please explain what is 24 doing in the expression? i understood 11 is the difference between midnight and 1:00pm , and how are we getting correct values by dividing 24 ?

Thanks a ton.

Conditions based on time stamps

swuehl — Wed, 31 Aug 2011 07:16:01 GMT

Hours are represented as decimal places / fractional part of a timestamp value, where the whole integer number represents days (1 per day). If a difference of 1 represents one day, 1/24 represents one hour (24 hours a day).

I noticed also that if your shipment is later than release date, it will still count to within 1 day, you could catch that if you want with an additional if.

Stefan