Average of records in a field

[gbab]

Hi there,

I have a the following table :

Cash Date
1000 December
-2000 January
.
.
3000 Current month

I want a query with the date of the current month and the average of the Cash field from Dec to the current month.

It works with the Avg aggregate function but when I show the date of the current month it doesn't work anymore because it only shows the last data, ie 3000 in the example.

Could you please advise me?

Thanks,
Guillaume

 

[statsman]

You could Sum the total cash amount and then divide by the number of months.

1st calculated field - SumCash:Sum([Cash])
2nd calculated field - CountMonth:Count([Date])
3rd calculated field - AvgCash:Sum([SumCash]/[CountMonth])

 

[gbab]

Thanks that's a good idea but it doesn't work for AvgCash. I have the following message : "Subqueries cannot be used in the expression AvgCash"

What should I do?

Thanks
Guillaume