Cross tab average formula

[mikeclark82]

Using crystal reports 9 and have a cross tab as follows:

Sales reps sales per month on a monthly basis. I would like to add a formula that will take the total amounts of all months so far and give an average of these values. The cross tab is run on a yearly scale of 01 april to 31 march the following year.

Any help would be very much appreciated.

 

[lbass]

Please identify your row and column fields, and also specify whether you are hoping to have the average appear across months for each salesperson or whether you are looking for an average of sales across months and salespeople.

My guess is that you will need to use a manual crosstab to accomplish this, although you could also use two separate crosstabs and potentially overlay one.

-LB

 

[mikeclark82]

Hi

Just the one row which will hold the average. The columns will be each salesperson i.e.

Sales Person 1 Sales person 2 etc
average Avg Avg

I would like to show the average across the months for each individual salesperson i.e sales amount / number of months it is run for the 2005/2006 financial year and is run at the end of every month to see a cumulative therefore simply dividing by 12 is not an option, i want to be able to divide by the number of months so far.

thanks

 

[lbass]

Then are you using Date (Month) as the row field? What happened to your initial summary, the sum of sales? Now it looks like you ONLY want the average.

Where is the crosstab located--in a group header/footer or in the report header/footer?

-LB

 

[mikeclark82]

it is located in the report header. I need an average of the total so far. i.e we are in August so April to August is 5 months so the total needs to be divided by 5. not necesarilly showing all the months but just the totals.

 

[lbass]

Try an inserted crosstab with salesperson as the column and then use the following formula as your summary field on which you will insert a SUM, not an average:

{table.salesamt}/(datediff("m",date({?year},04,01), currentdate)+1)

...where {?year} is a number parameter. If you want to use fiscal year as the parameter and you identify your fiscal year by the ending year, then change the formula to:

{table.salesamt}/(datediff("m",date({?fiscalyear}-1,04,01), currentdate)+1)

-LB