dividing running total by number of months 1

[CalgaryCR9]

Report name: number of FSP calls with contracted unit factor.rpt (crystal reports 9)

I have a report that counts the number of service calls {DistinctCount of SERVICE_CALL.SERVICE_CALL_ID} by month by vendor AND placed in footer of GROUP 1 (DATE).

Data looks like:
Total Calls Vendor A Vendor B
January 50 35 15
February 75 50 25
March 100 80 20
April 90 10 80

What I'm looking for is a Running total column for Total Calls divided by the number of Date Grouping (ie: running total of number of months)

Data should look like:

Total Calls Vendor A Vendor B Run Total
/months
January 50 35 15 50
February 75 50 25 62.5
March 100 80 20 75
April 90 10 80 78.5

Group 1 is Date printed by each month.
Group 2 is Vendor (a formula:
if {PART.PART_VENDOR_ID}=109
then "NETWORK"
else "UNIX"
Group 3 is Service Call ID

1. How do I get the running total for each month?
2. How do I divide the accumulating running total by the number of months?

So close, yet so far.

Thanks,
Joy

 

[lbass]

Create a formula {@month}:

month({table.date})

Then insert a running total {#months} that does a distinctcount of {@month}, evaluate for each record, reset never.

Create a second running total {#svccalls} that counts the call ID, reset never.

Then create a formula:

{#svccalls}/{#months}

...and place that in the date group footer.

-LB

 

[CalgaryCR9]

lbass,

Long time no chat.

I created @month:
month({SERVICE_CALL.CREATE_DT})

I inserted it into the details section and created the running total. I'm totally perplexed how the running total didn't produce a count of months, but a running total of calls.

Jan 50
Feb 125
mar 225
april 310 ....and so on.

I am suspecting this is due to my last group being SERVICE_CALL_ID and crytal is seeing 50, 125, 225, 310 calls with service call create dates.

#month is doing what #svccalls is supposed to do.

 

[lbass]

My fault, sorry. I think you inserted a count on {@month}, which only gives you the number of times the formula is executed. The formula was a bad idea. Change the month running total {#month} to do a distinctcount of {table.field}, evaluate using a formula:

onfirstrecord or
month({table.date)) <> month(previous({table.date}))

Reset never.

-LB

 

[CalgaryCR9]

lbass,

You hit the nail on the head. I did do a count vs distinct count. How could I have missed the obvious? Guess I was staring at it too long.

I have a residual problem.

Data:
Jan 05
Feb 05
Mar 05
continues for entire year
Jan 06
Feb 06

I end up with a running total of 12. What tweak needs to go in the formula to add the year as well as this report will likely be used for multiple years?

Thanks so much for giving me the pointers I need.

Joy

 

[lbass]

I think you used a distinctcount of {@month} in the running total this time, and that would have worked except for the years. Change the formula to:

date(year({table.date}),month({table.date}),1)

All dates will then reflect the first of each month/year combination. Use this in your running total, with no evaluation formula.

-LB

 

[CalgaryCR9]

Perfect lbass!!! I was just trying to look at some of the whacky teKtip arrays when I got the notificiation.

Your last posting resolved all.

I removed the reset formula & left evaluate at 'each record'. I know have an accumulating month count once the 12 month mark has passed.

You're awesome!