Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Time Buckets Syntax Error !!!!

Hi to all,

Can someone please help me correct the syntax of 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.

But however when I'm using a single expression and whenthe date is specified, its working fine, for example :

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

Gives me the correct answer, however when implementing it in the Time Bucket format, its showing a syntax error.

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

Thanks in advance.

7 Replies
Highlighted
Creator III
Creator III

Hi,

You send 'HH' as parameters but which function

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','> 4hours')))))

I assume you are trying to send it to the interval function

Maybe you try this :

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

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

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

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

Highlighted
Partner
Partner

NO its only giving the ANSWER '> 4 hours' and if I'm Tweaking up the equation (changing the DD-MM-YYYY to MM/DD/YYYY format) it's giving the answer '< 1 hour'

Highlighted
Creator III
Creator III

can you share a sample qvf?

Highlighted
Partner
Partner

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

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

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

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

You had missed 'Date' (the first date in this expression i've noted above)

Highlighted
Champion II
Champion II

Try like below

=Pick(Interval(Date((now()),'HH') -

Date(Date#(AND_allocatedDate2,'DD-MM-YYYY hh:mm:ss TT'),'HH'),'H'),

'<1 Hr','<2 Hr','<3 Hr','<4 Hr')

Highlighted
Partner
Partner

No, it isn't working

Highlighted
Partner
Partner

Hi,

The If condition should work as noted in my post above.

The problem could be with the Interval statement. It accepts an expression that can be formatted as a time. I don't think the '<1 hour', '<2 hours' etc satisfy this.

Instead if you just keep '1','2', it may work.