Time Tiers/ Bucket

[noman212]

Hi Community,

I need your help to calculate time in hours and Tier/Bucket as shown in attached image.

I have two date fields Reported_Time and Closed_Time and I need an interval in hours from that field.

Somehow, I succeeded to calculate the interval in hours by using the function.

      Interval((Timestamp(Floor(RESOLVEDATE)))- Timestamp(Floor(REPORTEDTIME)),'hh') AS RESOLVED_TIME_MINUTE

similarly, I need Tiers/Bucket like 0-1hrs, 1-8hrs and so on.

IF(Interval(Num((Floor(RESOLVEDATE)))- Num((Floor(REPORTEDTIME))),'hh') >= '00' and Interval(Num((Floor(RESOLVEDATE)))- Num((Floor(REPORTEDTIME))),'hh') <= '05', '0-5 hrs') as TIME

Please help to calculate the tiers like the attached image.

[kfoudhaily]

hello,

i'm not sure about what you want to do but you have a problem with your if condition

if syntax: if(condition and condition2, value if true, value if false)

in your expression you put: if(condition1 and condition2)

the if here is going to evaluate both of your conditions and just go to the next instruction and "TIME" field should be empty.

what is the output if both of your condition are correct?

what is the expected output if they are false?

regards,

[mtucholski · Accepted solution]

Hello,

For interval hours between dates you can use much simpler syntax:

round((REPORTEDTIME-RESOLVEDATE)*24) as hours

For as you call it "buckets" you coud use IntervalMatch:

IntervalMatch

Check the sample file with my solution.

cheers

[noman212]

That's what i want.

Thanks a lot.