Average based on # of Days in Month Crystal XI R2

[pungigis]

Have a report

Month Total Calls Daily Average
1/2010 914,416
2/2010 432,581
3/2010 426,258
4/2010 165,872
5/2010 246,191
6/2010 328,448

Need to be able to determine the daily average based on # of days in each month. The number in total calls is a count of a specific date.

Thanks

 

[briangriffin]

First get the calcluation working for the number of days in the month- something like this:

datediff("d",{firstofmonth},dateadd("m",1,{firstofmonth}))

The important thing is that the dates being passed have to be the first of the month. Since you're grouping on month, you can possibly do a min() on the datefield if you always have records on the first of the month, and in a call center you probably do.

Otherwise, you'll probably have to piece it together using the group name - something like:

date(right(GroupName ({YourDateField}, "monthly"),4) + "," + left(GroupName ({YourDateField}, "monthly"),instr(GroupName ({YourDateField}, "monthly"),"/") - 1) + ",1")

Once you're confident that the correct number of days is being returned, divide that calculation into your total calls to get the right average.

 

[lbass]

You should be able to use:

day(dateserial(year({table.date}), month({table.date})+1,1)-1)

...to get the number of days per month.

-LB

 

[SkipVought]

Laurie,

Does this return last day in CR?
[tt]
dateserial(year({table.date}), month({table.date})+1,[red]0[/red])
[/tt]
PS: I don't yave CR as yet, but word is, it's coming soon.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

Yes, I think so.

-LB (Linda)