time range within date range

[draacor]

I want to create a report that i can put in a date range, for example January to June. But i want to also break it down by time during each day. For example i do a report to see how many calls came through the ACD system. I put in a date range of 1/1/08 to 6/1/08. But i also want to see the average number of calls between 12AM and 7AM for this date range, and another average of calls between 7AM and 5PM, and so on.

Is this possible?

 

[CR85user]

Hi Draacor,

You certainly can do this.

I usually take a long route by creating seperate formulae for each time period e.g.:

//{@Countmorning}
if timevalue({tbl.datetime}) >= time(00,00,00) and
timevalue({tbl.datetime}) <= time(06,59,59) then 1 else 0

then add a running total on this formula to change based on date.

Repeat for other time periods etc.

There are other ways to approach this so look for any other suggestions. I just find this approach gives the most flexibility.

'J

 

[lbass]

You could add a group based on a formula like this:

if time({table.datetime}) in time(0,0,0) to time(6,59,59) then "12:00 to 6:59 AM" else
if time({table.datetime}) in time(7,0,0) to time(4,59,59) then "7:00 to 4:59 PM" else //etc.

Insert a group on this and then you can summarize your fields at the group level.

-LB

 

[draacor]

this is awesome, thanks for your help. Now i did notice that should i put everything in army time? otherwise how would the report know the difference between 5am and 5pm right?

 

[draacor]

OK the army time worked, another question. What if i want to do the same thing but just choose the weekend days or filter them out? So for example i want to run a date range from January to February but i want to exclude weekends. How would i do this?

 

[CR85user]

Add the following to your record selection:

not(dayofweek({table.date}) in [7,1])

This would then remove any dates within the selected date range where the day is Saturday or Sunday.

It may be that you want to make that conditional based on a parameter selection as an option for the report users.

'J

 

[draacor]

thank you CR85user for your help. That formula works great.

I do have one more question. I am trying to see the average number of agents taking calls for each of the time differences i mentioned in the beginning. For example, the report i have currently is doing a distinctcount of the agents per time variance. But the problem is its just counting the distinct number of agents for the entire reporting date range. What i want to do is do an average. So between 12am and 1am it is showing 6 total agents during that time but i want to know what the average number of agents have logged in during that time. Is there a way to have the report count the number for each day of hte date range and then i can divide it by the number of days in the range and it should come up with my average.

 

[lbass]

Sorry, my formula {@timeint} should have read:

if time({table.datetime}) in time(0,0,0) to time(6,59,59) then "12:00 to 6:59 AM" else
if time({table.datetime}) in time(7,0,0) to time(16,59,59) then "7:00 to 4:59 PM" else //etc.

You could use variables for the averages, as in:

//{@accum} to be placed in the {@timeint} group footer:
whileprintingrecords;
numbervar int12to7;
numbervar int7to5;
numbervar cnt12to7;
numbervar cnt7to5;
if {@timeint} = "12:00 to 6:59" then (
int12to7 := int12to7 + distinctcount({table.agent},{@timeint});
cnt12to7 := cnt12to7 + 1
);
if {@timeint} = "7:00 to 4:59 PM" then (
int7to5 := int7to5 + distinctcount({table.agent},{@timeint});
cnt7to5 := cnt7to5 + 1
);

Then in the report footer, create formulas like this:

//{@ave12to7}:
whileprintingrecords;
numbervar int12to7;
numbervar cnt12to7;
int12to7/cnt12to7

//{@ave7to5}:
whileprintingrecords;
numbervar int7to5;
numbervar cnt7to5;
int7to5/cnt7to5

-LB