Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Highlighted
Partner
Partner

Time Difference in Hours

Hi to all,

I want to find the 'Time Difference in hours'. However if the date is of the previous day, it shows the difference of hours in negative

For example:

Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#('3/7/2017 11:00:00 PM','M/D/Y h:mm:ss TT'),'hh'))

Where Now=('8/3/2017 8:00:00 AM')

The ans is comng negative as -14.

The main Equation I want to use this function is in Time Buckets.

The real time Bucket equation is given below.

Please help me rectify the error in the equation.

(Time Bucket Equation)

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 1,'< 1 hour',

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 2,'< 2 hours',

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 3,'< 3 hours',

if(Num#(Date(Date#(now(),'M/D/Y h:mm:ss TT'),'hh'))-Num#(Date(Date#(AND_allocatedDate2,'M/D/Y h:mm:ss TT'),'hh')) < 4,'< 4 hours','> 4 hours'))))


Where, AND_allocatedDate2 = Field Name


Thanks in advance.

1 Solution

Accepted Solutions
Silambarasan1306
Contributor III

Re: Time Difference in Hours

Hi Hema,

I think that expression won't work.

Try this,

=Interval(Date((now()),'HH')-Date(Date#('08-03-2017 11:00:00','DD-MM-YYYY hh:mm:ss'),'HH'))

View solution in original post

6 Replies
Partner
Partner

Re: Time Difference in Hours

hi,

Please try this set analysis

=Interval(Date(Date#(now(),'DD-MM-YYYY hh:mm:ss'),'HH')-Date(Date#('08-03-2017 11:30:00','DD-MM-YYYY hh:mm:ss'),'HH'))

Silambarasan1306
Contributor III

Re: Time Difference in Hours

Hi,

Try Something like below,

Load *,

     Time-Time2 as Diffrence,

     If(Time-Time2<1,'<1',

          If(Time-Time2<3,'<3',

               If(Time-Time2<5,'<5','<=10'))) as TimeBucket;

Load

     Hour(TimeDimension) as Time2

     Hour(Now()) as Time,

     From ....****;

Instead of maketime, you can use you time dimension.

Silambarasan1306
Contributor III

Re: Time Difference in Hours

Hi Hema,

I think that expression won't work.

Try this,

=Interval(Date((now()),'HH')-Date(Date#('08-03-2017 11:00:00','DD-MM-YYYY hh:mm:ss'),'HH'))

View solution in original post

Partner
Partner

Re: Time Difference in Hours

hi silambarasan,

I think your date format check and  Change the date format try it...

=Interval(Date(Date#(now(),'DD-MM-YYYY hh:mm:ss'),'HH')-Date(Date#('08-03-2017 11:30:00','DD-MM-YYYY hh:mm:ss'),'HH'))

Partner
Partner

Re: Time Difference in Hours

Tried it and it worked out perfectly, however I just had to do a minor change to get the answer only in hours.

Interval(Date((now()),'HH')-Date(Date#('08-03-2017 11:00:00 PM','DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH')

Just added ,'HH' and the answer came in hours.

This eqaution worked for me.

Thanks a lot Mate. Cheers

Partner
Partner

Re: Time Difference in Hours

Hi, Now can you please help me with this equation

Interval(if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 1,'< 1 hour',

if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 2,'< 2 hours',

if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 3,'< 3 hours',

if((Date((now()),'HH')-Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'HH') < 4,'< 4 hours',

'> 4 hours')))))


Where, And_allocatedDate2 is the field name.

The error is showing as ')' expected.

It will be of great help if you can guide me.