Filter the Query Base

The first step to identifying donors who have given consecutively for at least 5 years is to establish which data point will be looked at, sequentially. In this example, the data point to be counted sequentially is the  year of the gift.

To create and customize this join:

1. Add a Subquery Filter.

2. Name the join something that is user-friendly, such as "5 or More Years of Consecutive Giving."

3. Leave the Aggregate as Exists.

4. Click Join to add a new join to this subquery part.

5. In the dialog box, scroll down under the System header and select Sequential Value Groups.

   

6. The next step is to determine which data element should be used for this sequential value join. After clicking Continue, an export must be added. This export becomes the item that Slate will look at to determine if the values are consecutive.

7. Because in this example a gift's year is the item being counted sequentially, a join must be added to the Gift table in order to add return the date of the gift. Select Join and choose Gifts.

8. Add the export for Gift Date.

   

9. In its standard formatting, the Gift Date export will return a relatively unique value (date + time). To count the sequential years of giving, this export needs to be formatted in a way that only returns the elements to be counted consecutively - in this case, only the year element should be returned. Double Click the Gifts Date export and change the Format Type to "Date."

10. Enter "yyyy" for the Format Mask and click Save.

    

11. Click Save on the Sequential Value Groups part.

12. With the join added, insert filters as appropriate. Click Filter and select one of the filters from the Sequential Value Groups section - such as Count. 

    

13. Configure the Count setting to be >= 5. Click Save.

    

14. Save the subquery filter.

Add the Exports

Once the number of records has been appropriately filtered, the next step is to show the details about the years of consecutive giving as part of a subquery export. To configure the export to return the count of the consecutive year of giving, the first year in the series, and the most recent year in the series, follow the steps below:

1. Add a Subquery Export.

2. Repeat the steps outlined above to join in a Sequential Value Group that references a gift's year. This ensures the exports will be displaying the same data as the overall filter criteria for the query. 

   

3. With the join added, select which exports to include. Count, Min, and Max are available as exportable elements for the series. 

   

4. These exports can be arranged and literals can be added to provide context to the numbers that will be returned:

   

5. Provide a name to the subquery export part and click Save.