Basic Reporting Part IV FAQ: How to See the First (Earliest) or Last (Latest) Record in a String of Records

Basic Reporting Part IV FAQ: How to See the First (Earliest) or Last (Latest) Record in a String of Records

1. Contacts may have several records of the same type. For example, if a contact has been a member for a while and has renewed many times, they will have several membership transaction records. If the contact has made several donations to your organization over the course of several years, then they will have several donation records. If a contact has cycled on and off your Board of Directors as a board member several times, they will have several board member participation records.
2. There may be times when you want to see the first/earliest record in a string of records (such as the first time someone joined your board or the date of the first donation the contact ever made) or the last/latest record in the string (the most recent donation date or the current membership status).
3. The Min/Max function allows you to do this. By applying the Min (minimum) or Max (maximum) function to a date field, you can tell the report to show only the earliest (minimum) or latest (maximum) record.
4. For example, in this basic donation report, we can see that each donor has made multiple donations over many years.
5. If we wanted to see just the first/earliest donation for each contact, we could apply the Min/Max Filter to the donation date field. Now we will see just the first/earliest donation made by each donor.
6. The Min/Max Filter function is available in both Express View and Standard/Advanced reports. The examples show in this tutorial are using a Standard/Advanced report, but you will find the Min/Max function in Express View on the filters tab as well.
7. To use the Min/Max Filter, open your report in edit mode. Be sure that your report is grouped by Contact Name or Contact ID. Open the Filters tab/screen. Click the Group Min/Max
8. Add the applicable date field to your report. In this example, we want to see the first donation made by each contact, so we will add the Donation Date field to the Filter By area. If you wanted to see the first membership date, you could add the Membership Start Date field to the report or if you wanted to see the first event attended you could add the Event Registration date to your report, etc.
9. From the Minimum/Maximum drop down, select Minimum to get the earliest date.

10. Make sure your grouping field (Contact Name or Contact ID) is selected in the “For Each” box.

11. Click OK

12. Now when you run your report, only the earliest/first donation made by each contact will show.
13. We can also apply the Min/Max Filter to currency fields. For instance, if I want to see the greatest amount that each person has donated, I could follow the above steps, but replace Donation Date field with the Donation Amount field.
14. There is also another Min/Max option within Exago – the Min/Max Aggregate Function. This function is used in total/sub-total fields (in group, page, and report footers) to show the minimum or maximum amount in the selected field. The difference between the two options is that the Min/Max Filter is used/applies to the details section of a report, and the Min/Max Aggregate Function is used/applies to a group/report footer.
15. Because the AggMin/AggMax is an Agg formula, it can only be used in a group footer and not in the body of a report. AggMin and AggMax are two of the options available in the sub-total row(s) of Express View as well as available on the function editor on Standard/Advanced reports.
16. An example of how you might use the AggMin/AggMax Function would be if you wanted to see the range of donation amounts, from smallest amount to greatest amount, made by each of your donors. You could achieve this by using the AggMin and/or AggMax functions on your report.
17. To see the smallest amount donated by each donor, we would add a grouping on contact name or ID, add a group footer, and add the AggMin function to the group footer. To add the AggMin function to a Standard/Advanced report, add the Contact Name and Donation Amount fields to the group footer, highlight the cell with the Donation Amount, and then click the Formula Editor button (fx) on the toolbar.

18. Under the Aggregate category, you will find the AggMin function. Add it to the Formula Editor area, making sure the parentheses surround the Donation Amount field name.

19. Now when you run the report, you will see the smallest donation made by each contact (be sure to suppress the details section if you want to only see one row per contact).

20. If we also wanted to see the largest amount donated by each contact, we would add a blank column to the report, add the donation field to the footer again, and then repeat the above steps with the AggMax function/formula. In the example below, we’ve also renamed the column headers to make it easier to understand what data is in each column.

21. Now when you run the report, you will see both the smallest and the largest amount donated by each of your contacts/donors.