Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
chanin7893
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
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
Partner

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

View solution in original post

hemachandran
Partner
Partner

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
Partner
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
Partner
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.