New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for
Did you mean:
Highlighted
Contributor II

Time

What I'm trying to do is get the difference in time from when an order is placed ie (07:00) and if it has gone past 45 minutes ie (07:46) with out being picked

I've tried

Count ({1<test={">= 45"},[TYPE]={'1'},STATUS ={1}>} total TYPE)

test is from

Timestamp(time(Now(), 'hh:mm') - Timestamp("PICK TIME", 'hh:mm'), 'mm')  as test

[TYPE] is type of order

STATUS is if it has been picked  1 = No , 2 = yes

Any help would be appreciated.

3 Replies
Highlighted
MVP & Luminary

Re: Time

I would think you want to subtract now or pick time from Order Time, not pick time.  In any case, your issue is with the how you are treating the time interval.

1 = 1 whole day. 0.5 = 1/2 day or 12 hours.  Subtracting two timestamps that are 45 minutes apart yields the fraction  45/60/24 or 45/1440.  So your correct set expression would be:

Count ({1<test={">= 0.03125"},[TYPE]={'1'},STATUS ={1}>} total TYPE)

You could also calculate your "test" field as a minutes value by multiplying the difference * 1440 to yield minutes, and then use >= 45 in your set expression.

Highlighted
Contributor II

Re: Time

Thank you tried what you said but no joy

Interval(Timestamp(time('22:00', 'hh:mm'),'hh:mm')-Timestamp("PICK TIME", 'hh:mm'),'mm') as test2,           /  works
Interval(Timestamp(time(Now()),'hh:mm')-Timestamp("PICK TIME",'hh:mm'),'mm') as test,                                   /  Dont work
Interval(Timestamp("PART PICKED TIME",'hh:mm')- Timestamp("PICK TIME",'hh:mm'),'mm') as TimeDiff       /   works

Highlighted
MVP & Luminary

Re: Time

TimeStamp() is a formatting function and is incorrectly used here.  Perhaps you meant TimeStamp#() which reads string times, but I don't think is necessary here.  It's a common misconception that using TimeStamp() or Date() "extracts a portion..". It does not not. A good article is here: https://community.qlik.com/t5/Qlik-Sense-Enterprise-Documents/Dealing-with-date-formats-in-QlikView-...

Interval(Timestamp(time(Now()),'hh:mm')-Timestamp("PICK TIME",'hh:mm'),'mm') as test,   //  Dont work

I believe it did work, it just wasn't the calculation you expected.  Now() returns the current DateTime which numerically is something like 43472.416365741.  Your pick time field is only a time with no date component so it's value is something like 0.48958333333333.  Subtract and the difference is something like 43471 (days) and change.  Display that as minutes using Interval and you get a very big number.

To get the correct interval, "PICK TIME" should also include the date.