| Author |
Message |
rcoxe
Newbie
Joined: 19 Apr 2018
Location: United States
Online Status: Offline
Posts: 10
|
   Topic: Error- Must Be A Group That Matches This Field
Posted: 14 Apr 2021 at 10:28am |
|
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
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 15 Apr 2021 at 6:40am |
|
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
|
|
IP Logged |
|
rcoxe
Newbie
Joined: 19 Apr 2018
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 15 Apr 2021 at 6:54am |
|
That what I was thinking had to be done. Thanks for confirming.
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 29 Apr 2021 at 8:02am |
|
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
|
|
|
|
IP Logged |
|
rcoxe
Newbie
Joined: 19 Apr 2018
Location: United States
Online Status: Offline
Posts: 10
|
Posted: 30 Apr 2021 at 2:20am |
|
Thanks hilfy, that's perfect.
|
|
IP Logged |
|
|
|
