Crystal Report Crosstab Calculation of Average of each column based on no. of result

[naal]

Hi,

I am new in CR crosstab, so basically i am looking for avg. of each column with multiple results and no of counts of results.

Pls refer attached screen, in result section there are 5 result, now i want to calculate total of result / no of result 5 and put it either in Total field of new field. pls can anyone suggest how to do that.

crstb.jpg

I am attaching final screen i hope it help you to understand what i am looking for, i have multiple product, each product might be having 5 or 2 or 3 results respectively, i want to average of those results of each products respectively, please refer result screen shot.

result.jpg

[JWiseman]

You should first move the Total to the right...go to the Crosstab Expert > Customize > uncheck "Row Totals on Left". The Total column is where the reset for the manual running total should go.

In the Display String formula in the sample note that you can uncomment the last line to see what the manual running total is doing.

// un-comment the following line to see the running total values
//totext(value/counter,0);

Using the Grid functions in Crystal you should now be able to figure out what cells you need to add based on the indexes you see in the tooltips.

[naal]

Thank you so much for your continuous reply and help,

I already did that you just suggested to commit line etc, I saw counter is keep increasing and not reset on each column/ group.

I'll remove Row total and let you know.

[JWiseman]

Thanks for providing the result.jpg file. Have a look at the attached report...change the .txt extension to .rpt first.

This is possible using manual running totals in the cross-tab and then utilizing the "Display String" property for a cell. You should familiarize yourself with the Grid functions available for crosstabs...they can be a bit tricky at first.

Notes:

1. See the Format Field > Display String formulas for the green and red summaries.
2. There is a manual running total in the green summary Display String.
3. Hover over the crosstab cells to view the grid index values.
4. The red summary can be suppressed for display purposes, as it is used as a reset.
5. The 2nd summary in the crosstab is a dummy / placeholder and is only used to create the running total and display the value for each outer group.

crosstabwithaverageofsummaries.txt

[naal]

Hi jrwiseman,

Thank you so much for your reply and help,

I have tried manual running total Formula (in the green summary under Display String), but i am getting blank result (See the Blue text in image), i have again attached final screen to understand you better.

One more point in Summarized Field when i used sum of result (average) only one time i got error "Summary index is out of range" and when i add 2 sum of result under Summarized Field it got working but with no result. i am also attached crosstab expert screen

I hope you now able to address my issue, pls do let me know where i am going wrong.

One more point here counter should reset after each column/group because it keep counting and average become wrong( in above example), can't we used count function and divide with total result of each column/group ?

Thanks once again for all your help.

[ido_millet]

Crystal has no easy way to do totals of totals. You can try to handle the aggregation using variables.
Or you can use a Crystal UFL (User Function Library). Ken Hamady maintains a listing of 3rd-party UFLs here.

Here's an image showing how you can get an average of crosstab sums using my CUT Light UFL. The relevant user manual section is here.

[JWiseman]

Hi Nal Khand, in the Cross-Tab Expert you can add multiple Summaries which includes Average, Count, Distinct Count, etc. In the Customize Style tab, change the Summarized Fields option to Horizontal. If this is not what you're looking for, please provide a mock-up of what the final result should look like indicating what the various columns and rows and summaries are.

[naal]

I have added result screen pls refer

[naal]

I am attaching final screen i hope it help you to understand what i am looking for, i have multiple product, each product might be having 5 or 2 or 3 results respectively, i want to average of those results of each products respectively, please refer result screen shot.

[JWiseman]

Thanks for the picture. Please see my new answer further below. This requires the creation of a manual running total which is demonstrated in the sample report available below.