AVG based on no of columns

[ravula2000]

Hi
Here is the requirement.
The Cross tab has SalesGuyName as rows and each day sale as columns. Now say date range is Jan0109 to Jan3109. The Avg is defined in formula as Totalsales/days between that date range ie 30
So the problem is what happens if the salesguy does not have sale for say 10 days in the middle? So how to get 20 instead of 30 in the Avg formula?

Please help.

Thanks
Rav

 

[lbass]

What is the field you have entered as the column? Is it a date field set to change for every date? What is the current summary field?

-LB

 

[ravula2000]

Hi LB,
Thanks for your reply. The column is NoOfSales made by that saleman on one day.

Here is the report needed.

SalesPersonNm TotalAvg 1/1/09 1/5/09
David Jones 150 100 200
Mary 250 200 300

1/1/09 1/5/09 are sale dates. Saledate range is the parameter.

The problem is TotalAvg is a formula that has totalsales/noofdaysbetweenthedaterange.

Please advise.

Thanks
Rav

 

[lbass]

Please try again. In the crosstab expert, what field have you entered in the column area, and what field have you entered in the summary area?

-LB