topic Set Analysis between hours (times) in QlikView

Set Analysis between hours (times)

jcampbell474 — Tue, 11 Sep 2018 19:02:25 GMT

I have a requirement to calculate calls between hours. E.g., Number of calls, per 30-minute interval, between 9:00:00 AM and 5:00:00 PM.

I frequently do set analysis with dates, but never with times, and I can't get it to work.

Can anyone help me understand what I'm doing wrong?

=sum({<IntvlTime = {'>= 9:00:00 AM'}>} ACDReceived)

Note: IntvlTime is in the same format as the static time in the Set Analysis,.

Re: Set Analysis between hours (times)

neelamsaroha157 — Tue, 11 Sep 2018 19:23:21 GMT

May be you can convert your 12 hour time field into 24 hour timestamp and then use it in the set analysis. For example - Timestamp(Time, 'hh:mm:ss') as Time24Hours

and Set expression-

sum({<Time24Hours = {">=9:00:00"}>}ACDReceived)

Re: Set Analysis between hours (times)

neelamsaroha157 — Tue, 11 Sep 2018 19:25:10 GMT

didn't add the 9-5 condition above-

sum({<Time24Hours = {">=9:00:00<=17:00:00"}>}ACDReceived)

Re: Set Analysis between hours (times)

jcampbell474 — Tue, 11 Sep 2018 19:40:10 GMT

Thank you, but it doesn't work. The sum is zero'd out.

Here's before:

After (yours is the 3rd column)

Re: Set Analysis between hours (times)

neelamsaroha157 — Tue, 11 Sep 2018 20:08:31 GMT

This is working for the sample that I picked, not sure whats the issue on your side.

Can you share sample data?

Re: Set Analysis between hours (times)

jcampbell474 — Wed, 12 Sep 2018 18:43:06 GMT

Thank you for the help!

I figured it out. It's something you wouldn't have known without the sample data.

I had to add 'frac' to the loadscript to drop the date / extract the time.

Timestamp(frac(intstartdte), 'hh:mm:ss') as IntvlTime

Works like a charm, now!

Thanks again!

Jason