Error- Must Be A Group That Matches This Field

I have a report that shows me transactions and it is grouped by the document date (field is called DocDate). In the group options I have it set to sort by month. I am trying to create a formula that gives me an average amount per device. My formula is basically:

SUM({Amount},{DocDate})/SUM({Devices},{DocDate})

I get the error that there must be a group that matches the DocDate field. When I change the sort option to sort by day I do not get the error. How can I modify my formula to avoid this error?

Thanks for the help.

Edited by rcoxe - 14 Apr 2021 at 10:33am

my first thought is create a formula that returns the month/year and then use that as your grouping.

something simple like:
totext(month({DocDate}), 0, "") + "/" + totext(year({DocDate}), 0, "")

Then in the grouping criteria, select your formula.
At least this is how I would try to get around the error.

While you are sorting by month, the grouping field is a date...I am thinking that Crystal is getting confused.

HTH

That what I was thinking had to be done. Thanks for confirming.

Another way to do this would be to use a summary condition as the third parameter to the Sum() function. This would let you tell it to sum "for each month" instead of for each day, which is the default. So the formula would look something like:

SUM({Amount},{DocDate},"monthly")/SUM({Devices},{DocDate}, "monthly")

-Dell

Thanks hilfy, that's perfect.