Charting Distinct Count Averages

[mtownbound]

I'm trying to chart averages from distinct entries grouped by technicians. Here is a sample:

Group Ticket# Time Spent
Tech A #87234 15
#88343 20
#88343 30
#88431 50

The formula field that I've created takes the distinct count of the trouble tickets so that the average is 38.33 instead of incorrectly couting the lines for an average of 28.75. The issue is that I can't chart that in a bar chart.

Any ideas?

Thanks!!

 

[beacon5]

What version of Crystal Reports are you using? I'm using 2008, so my response is going to be based on my version. You made to alter the steps slightly to get it to work with your version (specifically the part about the chart toward the end).

Do you have a group for the ticket numbers under the technician group?

If so, insert a two summaries in the group footer for the ticket number: one summary will be a distinct count of the ticket numbers and the other will be a sum of the time spent.

Once you've add the summaries, copy the summaries to the group footer for the technician. This should show you the correct number of tickets per technician and the correct addition of the time spent.

Then, you can create a formula (I'm calling it @MyAverage) like the following (substituting the real names of the fields for the ones I've used):

Sum ({MyTable.TimeSpent})/DistinctCount ({MyTable.TicketNumber})

Put this formula in the group footer for the technician as well and you should have the correct average for that technician.

Once you've gotten this far (assuming everything has worked so far), insert a chart. When the Chart Expert appears, choose the type of chart you want on the first tab. On the Data tab, put the Technician field in the first box under the On Change Of drop down. In the Show Value(s) box, add the @MyAverage formula.

At this point, you can click okay and the chart should work the way you intend it to.

Hope this helps,
beacon

 

[mtownbound]

Thanks for the response beacon!

This seems pretty similar to what I've done already. If I have the group for the technician and add a group for the tickets, then the distinct count for the tickets group will always be 1 and I still have to tally the count of tickets and the sum of the time in the technician group footer.


Thanks again!!

 

[lbass]

You need to show the content of the formula you used to get the average of the distinctcount.

-LB

 

[mtownbound]

lbass,

It's nothing out of the ordinary, it's just evaluating for each group.

{#RTotal1}/{#RTotal2}

Do I need to use a subreport??

 

[lbass]

Please explain the exact set up of each running total.

-LB

 

[mtownbound]

In group footer:
#RTotal1 = sum{table.timespent}
#RTotal2 = distinctcount{table.ticketid}

Both reset on group

@Average = {#RTotal1}/{#RTotal2}

 

[mtownbound]

I probably should start a new thread, but not the best at variables, so need a little assistance. I need to add and chart a set number of id's from an old database.

So I need my grouped totals to essentially be:

@formulafield = #RTotal1+40//40 being number of archived tickets.