Answers.Body: <p>I worked it out, you need two SK queries, not three, but you have to group by a partial month.  Screenshots of the two queries are below, along with exports of the queries.
  <a href="https://i.sstatic.net/6AQto.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/6AQto.png" alt="enter image description here" /></a>
  <a href="https://i.sstatic.net/DZIT7.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/DZIT7.png" alt="enter image description here" /></a></p>
  <pre><code>[
    [
      &quot;SavedSearch&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;First_donations&quot;,
            &quot;label&quot;: &quot;First donations&quot;,
            &quot;form_values&quot;: null,
            &quot;mapping_id&quot;: null,
            &quot;search_custom_id&quot;: null,
            &quot;api_entity&quot;: &quot;Contact&quot;,
            &quot;api_params&quot;: {
              &quot;version&quot;: 4,
              &quot;select&quot;: [
                &quot;id&quot;,
                &quot;display_name&quot;,
                &quot;MIN(Contact_Contribution_contact_id_01.id) AS MIN_Contact_Contribution_contact_id_01_id&quot;,
                &quot;MIN(Contact_Contribution_contact_id_01.receive_date) AS MIN_Contact_Contribution_contact_id_01_receive_date&quot;
              ],
              &quot;orderBy&quot;: [],
              &quot;where&quot;: [],
              &quot;groupBy&quot;: [
                &quot;id&quot;
              ],
              &quot;join&quot;: [
                [
                  &quot;Contribution AS Contact_Contribution_contact_id_01&quot;,
                  &quot;INNER&quot;,
                  [
                    &quot;id&quot;,
                    &quot;=&quot;,
                    &quot;Contact_Contribution_contact_id_01.contact_id&quot;
                  ]
                ]
              ],
              &quot;having&quot;: []
            },
            &quot;expires_date&quot;: null,
            &quot;description&quot;: &quot;Do not delete - necessary for \&quot;New One-Time Donors by Month\&quot;&quot;
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;
        ]
      }
    ],
    [
      &quot;SearchDisplay&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;FirstDonationsDbEntity1&quot;,
            &quot;label&quot;: &quot;First donations&quot;,
            &quot;saved_search_id.name&quot;: &quot;First_donations&quot;,
            &quot;type&quot;: &quot;entity&quot;,
            &quot;settings&quot;: {
              &quot;sort&quot;: [],
              &quot;columns&quot;: [
                {
                  &quot;type&quot;: &quot;field&quot;,
                  &quot;key&quot;: &quot;id&quot;,
                  &quot;dataType&quot;: &quot;Integer&quot;,
                  &quot;label&quot;: &quot;Contact ID&quot;,
                  &quot;spec&quot;: {
                    &quot;name&quot;: &quot;id&quot;,
                    &quot;data_type&quot;: &quot;Integer&quot;,
                    &quot;suffixes&quot;: null,
                    &quot;options&quot;: false,
                    &quot;fk_entity&quot;: null,
                    &quot;original_field_name&quot;: &quot;id&quot;,
                    &quot;original_field_entity&quot;: &quot;Contact&quot;
                  }
                },
                {
                  &quot;type&quot;: &quot;field&quot;,
                  &quot;key&quot;: &quot;display_name&quot;,
                  &quot;dataType&quot;: &quot;String&quot;,
                  &quot;label&quot;: &quot;Display Name&quot;,
                  &quot;spec&quot;: {
                    &quot;name&quot;: &quot;display_name&quot;,
                    &quot;data_type&quot;: &quot;String&quot;,
                    &quot;suffixes&quot;: null,
                    &quot;options&quot;: false,
                    &quot;fk_entity&quot;: null,
                    &quot;original_field_name&quot;: &quot;display_name&quot;,
                    &quot;original_field_entity&quot;: &quot;Contact&quot;
                  }
                },
                {
                  &quot;type&quot;: &quot;field&quot;,
                  &quot;key&quot;: &quot;MIN_Contact_Contribution_contact_id_01_id&quot;,
                  &quot;dataType&quot;: &quot;Integer&quot;,
                  &quot;label&quot;: &quot;Donation ID&quot;,
                  &quot;spec&quot;: {
                    &quot;name&quot;: &quot;MIN_Contact_Contribution_contact_id_01_id&quot;,
                    &quot;data_type&quot;: &quot;Integer&quot;,
                    &quot;suffixes&quot;: null,
                    &quot;options&quot;: false
                  }
                },
                {
                  &quot;type&quot;: &quot;field&quot;,
                  &quot;key&quot;: &quot;MIN_Contact_Contribution_contact_id_01_receive_date&quot;,
                  &quot;dataType&quot;: &quot;Timestamp&quot;,
                  &quot;label&quot;: &quot;Donation Date&quot;,
                  &quot;spec&quot;: {
                    &quot;name&quot;: &quot;MIN_Contact_Contribution_contact_id_01_receive_date&quot;,
                    &quot;data_type&quot;: &quot;Timestamp&quot;,
                    &quot;suffixes&quot;: null,
                    &quot;options&quot;: false
                  }
                }
              ]
            },
            &quot;acl_bypass&quot;: false
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;,
          &quot;saved_search_id&quot;
        ]
      }
    ]
  ]
  </code></pre>
  <pre><code>[
    [
      &quot;SavedSearch&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;Partial_Donation_Dates&quot;,
            &quot;label&quot;: &quot;Partial Donation Dates&quot;,
            &quot;form_values&quot;: null,
            &quot;mapping_id&quot;: null,
            &quot;search_custom_id&quot;: null,
            &quot;api_entity&quot;: &quot;SK_FirstDonationsDbEntity1&quot;,
            &quot;api_params&quot;: {
              &quot;version&quot;: 4,
              &quot;select&quot;: [
                &quot;COUNT(id) AS COUNT_id&quot;,
                &quot;EXTRACT(YEAR_MONTH FROM MIN_Contact_Contribution_contact_id_01_receive_date) AS EXTRACT_MIN_Contact_Contribution_contact_id_01_receive_date&quot;
              ],
              &quot;orderBy&quot;: [],
              &quot;where&quot;: [],
              &quot;groupBy&quot;: [
                &quot;MIN_Contact_Contribution_contact_id_01_receive_date&quot;
              ],
              &quot;join&quot;: [],
              &quot;having&quot;: []
            },
            &quot;expires_date&quot;: null,
            &quot;description&quot;: &quot;Do not delete - necessary for \&quot;New One-Time Donors by Month\&quot;&quot;
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;
        ]
      }
    ]
  ]
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-07-25T21:44:34.767Z
Answers.Text: This will ONLY show you the count - you can't use this to see totals, for instance.  I've asked Coleman about it, hopefully there's a fix coming soon.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-07-25T18:45:48.503Z
Answers.LastActivityDate: 2023-07-25T18:45:48.503Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>This seems a reasonable enough request but I can't put it together.</p>
  <p>Here's what I've tried:</p>
  <ul>
  <li>Contribution Summary Report - Does not contain the &quot;Contribution Ordinality&quot; filter (though adding it might be my best option).</li>
  <li>Summary Fields - this is flaky with real-time updates, and scheduled updates lock the database.</li>
  <li>SearchKit - I created a search that grouped contributions by contact ID and gave the minimum ID.  I saved that as a new SearchKit entity (this is new in 5.63).  However, I couldn't join this new entity to other entities, and trying to make an entity from the entity gave me a database error.</li>
  </ul>
  
Tags: <civicontribute><civireport><reports><searchkit>
Title: How can I display a count of new donors by month?
ThreadText: # How can I display a count of new donors by month?
  
  (Asked by: username_0 on 2023-07-25)
  
  This seems a reasonable enough request but I can't put it together.
  
  Here's what I've tried:
  
  * Contribution Summary Report - Does not contain the "Contribution Ordinality" filter (though adding it might be my best option).
  * Summary Fields - this is flaky with real-time updates, and scheduled updates lock the database.
  * SearchKit - I created a search that grouped contributions by contact ID and gave the minimum ID. I saved that as a new SearchKit entity (this is new in 5.63). However, I couldn't join this new entity to other entities, and trying to make an entity from the entity gave me a database error.
  
  **Tags:** civicontribute, civireport, reports, searchkit
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2023-07-25. Score: 2
  
  I worked it out, you need two SK queries, not three, but you have to group by a partial month.  Screenshots of the two queries are below, along with exports of the queries.
  
  
  [
    [
      "SavedSearch",
      "save",
      {
        "records": [
          {
            "name": "First_donations",
            "label": "First donations",
            "form_values": null,
            "mapping_id": null,
            "search_custom_id": null,
            "api_entity": "Contact",
            "api_params": {
              "version": 4,
              "select": [
                "id",
                "display_name",
                "MIN(Contact_Contribution_contact_id_01.id) AS MIN_Contact_Contribution_contact_id_01_id",
                "MIN(Contact_Contribution_contact_id_01.receive_date) AS MIN_Contact_Contribution_contact_id_01_receive_date"
              ],
              "orderBy": [],
              "where": [],
              "groupBy": [
                "id"
              ],
              "join": [
                [
                  "Contribution AS Contact_Contribution_contact_id_01",
                  "INNER",
                  [
                    "id",
                    "=",
                    "Contact_Contribution_contact_id_01.contact_id"
                  ]
                ]
              ],
              "having": []
            },
            "expires_date": null,
            "description": "Do not delete - necessary for \"New One-Time Donors by Month\""
          }
        ],
        "match": [
          "name"
        ]
      }
    ],
    [
      "SearchDisplay",
      "save",
      {
        "records": [
          {
            "name": "FirstDonationsDbEntity1",
            "label": "First donations",
            "saved_search_id.name": "First_donations",
            "type": "entity",
            "settings": {
              "sort": [],
              "columns": [
                {
                  "type": "field",
                  "key": "id",
                  "dataType": "Integer",
                  "label": "Contact ID",
                  "spec": {
                    "name": "id",
                    "data_type": "Integer",
                    "suffixes": null,
                    "options": false,
                    "fk_entity": null,
                    "original_field_name": "id",
                    "original_field_entity": "Contact"
                  }
                },
                {
                  "type": "field",
                  "key": "display_name",
                  "dataType": "String",
                  "label": "Display Name",
                  "spec": {
                    "name": "display_name",
                    "data_type": "String",
                    "suffixes": null,
                    "options": false,
                    "fk_entity": null,
                    "original_field_name": "display_name",
                    "original_field_entity": "Contact"
                  }
                },
                {
                  "type": "field",
                  "key": "MIN_Contact_Contribution_contact_id_01_id",
                  "dataType": "Integer",
                  "label": "Donation ID",
                  "spec": {
                    "name": "MIN_Contact_Contribution_contact_id_01_id",
                    "data_type": "Integer",
                    "suffixes": null,
                    "options": false
                  }
                },
                {
                  "type": "field",
                  "key": "MIN_Contact_Contribution_contact_id_01_receive_date",
                  "dataType": "Timestamp",
                  "label": "Donation Date",
                  "spec": {
                    "name": "MIN_Contact_Contribution_contact_id_01_receive_date",
                    "data_type": "Timestamp",
                    "suffixes": null,
                    "options": false
                  }
                }
              ]
            },
            "acl_bypass": false
          }
        ],
        "match": [
          "name",
          "saved_search_id"
        ]
      }
    ]
  ]
  
  [
    [
      "SavedSearch",
      "save",
      {
        "records": [
          {
            "name": "Partial_Donation_Dates",
            "label": "Partial Donation Dates",
            "form_values": null,
            "mapping_id": null,
            "search_custom_id": null,
            "api_entity": "SK_FirstDonationsDbEntity1",
            "api_params": {
              "version": 4,
              "select": [
                "COUNT(id) AS COUNT_id",
                "EXTRACT(YEAR_MONTH FROM MIN_Contact_Contribution_contact_id_01_receive_date) AS EXTRACT_MIN_Contact_Contribution_contact_id_01_receive_date"
              ],
              "orderBy": [],
              "where": [],
              "groupBy": [
                "MIN_Contact_Contribution_contact_id_01_receive_date"
              ],
              "join": [],
              "having": []
            },
            "expires_date": null,
            "description": "Do not delete - necessary for \"New One-Time Donors by Month\""
          }
        ],
        "match": [
          "name"
        ]
      }
    ]
  ]
  
  > **Comments:**
  > **username_0**: This will ONLY show you the count - you can't use this to see totals, for instance.  I've asked Coleman about it, hopefully there's a fix coming soon.  
  
  ---
  
  