I have a search requirement (Customer related custom records) that must return the Sum of a value field [Total Qty] on any records that share the same Max [Month End Date].
In the record sets there can be multiple records with the same [Month End Date], but some may have [Month End Date] prior to the most recent month (last Month). This means I can’t just use a Criteria filter to limit to a Relative Date range (like In Last Month) as the last [Month End Date] may be several months ago. Regardless of how far in the past the max [Month End Date] is I need to sum the [Total Qty] related to that date.
In this simple example the Customer has multiple records in each of the prior months:
In the Search I have two Grouped fields SUM(Total Qty) and MAX(Month End Date), and it associates to Customer via an Available Filter so that it can be used as a Custom Field search result on the Customer record.
The result I’m looking for would be the summed [Total Qty] = 100 (65 + 35), however In the Search I created using SUM(Total Qty) and MAX(Month End Date) it returns 215 for the Sum of [Total Qty] and 10/31/2020 for the Max [Month End Date]. The result in the Sum should be 100 (65 + 35) but it’s summing all records rather than only those that are related to the Max date.
How can I limit the SUM to just the MAX [Month End Date]?
I’ve tried tinkering with Summary Criteria filtering but I can’t seem to get that to work either.
Thanks for any help or tips.
