Help please in identifying calls in every 15 minute period across a 24 hour period.
For exmaple if there 5 are calls made say time interval 9:00 AM to 9:20 AM
Time No of calls
9:00:17 1
9:07:17 1
9:07:18 1
9:14:22 1
9:17:00 1
9:19:46 1
If the raw data is like above
the there must be two buckets like that it should be created for 24 hrs
Buckets Calls
9:00:00 4
9:15:00 2
babu_272 
(BOB member since 2010-07-28)
I would create a variable to identify the bucket, then count entries falling into each bucket. I’d identify the bucket by calculating (something like)
bucket = (hours * 4) + (minutes integer divide 15)Bill K (BOB member since 2011-02-24)
Hi ,
This didn’t create the intended bucket
Can you suggest me any other better option
babu_272 (BOB member since 2010-07-28)
very messy. I would push the logic to the database level. but if you must in webi then you can use formatdate() function to return parts of the time assuming they are in date format otherwise you’d have to convert to a date using todate() and then extract the hour in one variable and minute in another. then you create a variable referencing these with a monster if statement. also hopefully you should use the 24 hour clock where 1 is 1am and 13 is 1pm
=if [hour]=“0” and [min]<=15 then “0:00” else if [hour]=0 and [min]<=30 then “0:15” etc, so you’d have one 4 conditions for each hour times 24=96 conditions in your if statement.
erik.stenson 
(BOB member since 2012-07-30)
Hi,
Thanks for your reply.
Doing this is really dam messy and need to create many if then else , without using can anyone suggest better approach?
If DB level how to achieve?
babu_272 (BOB member since 2010-07-28)
Something like
bucket = formatdate([calltime];“HH”)+":"+ formatnumber(15*floor(tonumber(formatdate([calltime];“mm”))/15);“00”)
mikeil (BOB member since 2015-02-18)
that’s pretty cool, mikeil!
erik.stenson (BOB member since 2012-07-30)