Answers.Body: <p>This is not necessarily an answer on the aggregate field but a potential workaround may be running the 'Top Donors' report with the criteria set to your requirements and then adding those contacts to a group. You should then be able to use that to find contacts that are in the top donors group but have no current membership (or whatever other membership criteria you select) via the 'Find Members' search function. Maybe this helps and will get you the data you need.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-17T20:41:22.130Z
Answers.Text: Thanks, Rebecca! See my response to Alan. With summary fields, I was able to do more or less what you described. Not with Find Members, but by including and excluding groups to create a smart group with what I needed. Not _quite_ what I was setting out to do, but turns out to be a great solution. Thanks!
Answers.UserDisplayName: RiverRunner
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-16T17:10:19.397Z
Answers.LastActivityDate: 2019-12-16T17:10:19.397Z
Answers.UserDisplayName: RebeccaTregenna
Answers.Body: <p>So, to elaborate a bit on my comments with the solution I arrived at. I used summary fields, relying on the built-in Total Contributions in the Last 12 Months field. </p>
  
  <p>Then I created a smart group called Membership Candidates that contains everyone who donated the threshold amount in the last 12 months.</p>
  
  <p>Then I used a group I already had called Current Members.</p>
  
  <p>Finally I created a smart group that includes Membership Candidates and excludes Current Members (and another one, Organizations), and that gives me the list I needed. It doesn't display the 12-month donation total or the last membership expiration, but that turns out not to matter.</p>
  
  <p>Thanks, everyone!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-17T20:46:12.927Z
Answers.LastActivityDate: 2019-12-17T20:46:12.927Z
Answers.UserDisplayName: RiverRunner
Answers.Body: <p>Use the summary fields extension here: </p>
  
  <pre><code>https://civicrm.org/extensions/summary-fields
  </code></pre>
  
  <p>It will create some custom fields and calculate the totals, minimums etc. values that you are trying to generate on the fly. That way you don't have to do all those joins. Aggregation is hard.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-17T20:39:34.967Z
Answers.Text: Using summary fields turned out to be what I needed to do. With them, along with the include and exclude groups mentioned by Rebecca, got me to where I need to be. Thanks!
Answers.UserDisplayName: RiverRunner
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-16T19:41:45.403Z
Answers.LastActivityDate: 2019-12-16T19:41:45.403Z
Answers.UserDisplayName: Alan Dixon
Body: <p>I need to create a report with a list of all donors, the total that they've contributed over the last 12 months, when their last membership expired, and a couple contact items like email address. (If they meet a certain minimum contribution amount, I need to give them a 12-month membership.) I'm using the Data Processor extension on a WordPress site (everything is up to date). </p>
  
  <p>I have the overall data processor created, with data sources, output, fields, and a filter for 12 months worth of contributions. But right now it is giving me one row for each individual contribution and membership expiration (apparently a Cartesion product query). This seems to suggest that I need to aggregate the contribution Total Amount and Membership Expiration Date.</p>
  
  <p>How can I do that? Adding an aggregate field is fairly straightforward: just select the field you want to add. There are no other options. Yet it doesn't seem to do any aggregation.</p>
  
  <p>My Google-fu has let me down finding any documentation about aggregation fields in data processor beyond a mention of the feature.</p>
  
  <p>How can I get an aggregation to work? Or am I misunderstanding what aggregation does? How can I accomplish what I need?</p>
  
  <p>I suspect that an aggregation may not work with the membership expiration date. If not, how can I see in the report whether they are a current member?</p>
  
  <p>Thanks!</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-12-16T18:36:38.513Z
Comments.Text: Hi, @jaapjansma,  the creator of the extension, should be able to help with an answer. Also see the repo Issues and docs at https://lab.civicrm.org/extensions/dataprocessor . Check out the ReportPlus extension for doing totals /aggregation, at https://lab.civicrm.org/extensions/reportplus/tree/2.x .
Comments.UserDisplayName: Joe McLaughlin
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-12-17T20:38:15.423Z
Comments.Text: Thanks, Joe! I still don't see much about aggregation at the docs, but you helped me find that a comment that a data processor aggregation is like a group by clause in SQL server. So it's a very useful feature, but wasn't what I needed. So that's really good to know.
Comments.UserDisplayName: RiverRunner
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-12-17T23:32:07.653Z
Comments.Text: cool and glad Alan pointed you to the correct answer and duh on me :) . The Report Plus extension does totals/aggregation as well but Summary Fields works, yay.
Comments.UserDisplayName: Joe McLaughlin
Tags: <civimember><contributions>
Title: Need Input on Use of an Aggregation in the Data Processor Extension
ThreadText: # Need Input on Use of an Aggregation in the Data Processor Extension
  
  (Asked by: username_0 on 2019-12-16)
  
  I need to create a report with a list of all donors, the total that they've contributed over the last 12 months, when their last membership expired, and a couple contact items like email address. (If they meet a certain minimum contribution amount, I need to give them a 12-month membership.) I'm using the Data Processor extension on a WordPress site (everything is up to date).
  
  I have the overall data processor created, with data sources, output, fields, and a filter for 12 months worth of contributions. But right now it is giving me one row for each individual contribution and membership expiration (apparently a Cartesion product query). This seems to suggest that I need to aggregate the contribution Total Amount and Membership Expiration Date.
  
  How can I do that? Adding an aggregate field is fairly straightforward: just select the field you want to add. There are no other options. Yet it doesn't seem to do any aggregation.
  
  My Google-fu has let me down finding any documentation about aggregation fields in data processor beyond a mention of the feature.
  
  How can I get an aggregation to work? Or am I misunderstanding what aggregation does? How can I accomplish what I need?
  
  I suspect that an aggregation may not work with the membership expiration date. If not, how can I see in the report whether they are a current member?
  
  Thanks!
  
  **Tags:** civimember, contributions
  
  > **Comments:**
  > **username_1**: Hi, @jaapjansma,  the creator of the extension, should be able to help with an answer. Also see the repo Issues and docs at https://lab.civicrm.org/extensions/dataprocessor . Check out the ReportPlus extension for doing totals /aggregation, at https://lab.civicrm.org/extensions/reportplus/tree/2.x .  
  > **username_0**: Thanks, Joe! I still don't see much about aggregation at the docs, but you helped me find that a comment that a data processor aggregation is like a group by clause in SQL server. So it's a very useful feature, but wasn't what I needed. So that's really good to know.  
  > **username_1**: cool and glad username_3 pointed you to the correct answer and duh on me :) . The Report Plus extension does totals/aggregation as well but Summary Fields works, yay.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_3 on 2019-12-16. Score: 4 | Accepted answer
  
  Use the summary fields extension here:
  
  ```
  https://civicrm.org/extensions/summary-fields
  
  ```
  
  It will create some custom fields and calculate the totals, minimums etc. values that you are trying to generate on the fly. That way you don't have to do all those joins. Aggregation is hard.
  
  > **Comments:**
  > **username_0**: Using summary fields turned out to be what I needed to do. With them, along with the include and exclude groups mentioned by Rebecca, got me to where I need to be. Thanks!  
  
  ---
  
  ### Answer by username_2 on 2019-12-16. Score: 2
  
  This is not necessarily an answer on the aggregate field but a potential workaround may be running the 'Top Donors' report with the criteria set to your requirements and then adding those contacts to a group. You should then be able to use that to find contacts that are in the top donors group but have no current membership (or whatever other membership criteria you select) via the 'Find Members' search function. Maybe this helps and will get you the data you need.
  
  > **Comments:**
  > **username_0**: Thanks, Rebecca! See my response to username_3. With summary fields, I was able to do more or less what you described. Not with Find Members, but by including and excluding groups to create a smart group with what I needed. Not _quite_ what I was setting out to do, but turns out to be a great solution. Thanks!  
  
  ---
  
  ### Answer by username_0 on 2019-12-17. Score: 2
  
  So, to elaborate a bit on my comments with the solution I arrived at. I used summary fields, relying on the built-in Total Contributions in the Last 12 Months field.
  
  Then I created a smart group called Membership Candidates that contains everyone who donated the threshold amount in the last 12 months.
  
  Then I used a group I already had called Current Members.
  
  Finally I created a smart group that includes Membership Candidates and excludes Current Members (and another one, Organizations), and that gives me the list I needed. It doesn't display the 12-month donation total or the last membership expiration, but that turns out not to matter.
  
  Thanks, everyone!
  
  
  ---
  
  