topic Re: Time Tiers/ Bucket in QlikView

Time Tiers/ Bucket

noman212 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Time Tiers/ Bucket

kfoudhaily — Thu, 09 Aug 2018 09:44:10 GMT

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,

Re: Time Tiers/ Bucket

mtucholski — Thu, 09 Aug 2018 10:03:33 GMT

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

Re: Time Tiers/ Bucket

noman212 — Thu, 09 Aug 2018 10:32:50 GMT

That's what i want.

Thanks a lot.