Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
chanin7893
Partner - Contributor III
Partner - Contributor III

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
Creator III
Creator III

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
hemachandran
Partner - Creator
Partner - Creator

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
Creator III
Creator III

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
Creator III
Creator III

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'))

hemachandran
Partner - Creator
Partner - Creator

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'))

chanin7893
Partner - Contributor III
Partner - Contributor III
Author

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

chanin7893
Partner - Contributor III
Partner - Contributor III
Author

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.