Answers.Body: <p>Yes you can do field calculations in SearchKit and use them as filters</p>
  <ol>
  <li><p><strong>Field Calculation:</strong> In the &quot;Select Fields&quot; section, click the little Fx button next to your field and select Math: Arithmetic. This will let you select another field and a comparison operator (in this case you want to subtract one field from the other).</p>
  </li>
  <li><p><strong>Filtering:</strong> Now that your calculation is done you can use the HAVING clause to filter results by the value of the calculated column.</p>
  </li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-08T16:14:59.857Z
Answers.Text: Thank you. For me, under the "fx" is "Math: Aritmetic" available only for some fields, like Contact ID (which type is number), but not for the fields, which type are eg. currency. And I really don't now, if it is a bug, a feature, a development request or my failure.
Answers.UserDisplayName: Pál Rudan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-09T15:13:56.450Z
Answers.Text: Is "Promised" a custom field? If so you should make it a numeric field instead of a text field.
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-10T15:58:42.693Z
Answers.Text: No, "Promised" is the CiviCRM built-in Pledge's "Amount" field. This is stored in the database a number with currency. (Sorry, I user CiviCRM in Hungarian language, and sometimes it's wired and not quite easy to translate "back" the fields name to English.)
Answers.UserDisplayName: Pál Rudan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-10T16:53:26.827Z
Answers.Text: Ok I'm not sure why it wouldn't work then. Arithmetic works on all numeric fields. What version of CiviCRM are you using?
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-10T16:58:05.100Z
Answers.Text: Continuously (since 2 years) the latest, now 5.77.0, with Drupal 7 (but I think, it doesn't matters).
Answers.UserDisplayName: Pál Rudan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-10T21:10:48.247Z
Answers.Text: Could you try to reproduce the problem on https://dmaster.demo.civicrm.org/
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-14T18:11:15.057Z
Answers.Text: Yes. https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/47, on the "Select fields" tab, before the "Total amount" field, the available options are only the "Aggregate" options.
Answers.UserDisplayName: Pál Rudan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-14T20:29:44.103Z
Answers.Text: I see, it's because when you add the `GROUP BY contact id` it means the columns have to be aggregated because you will have more than one contribution per row.
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-09-06T17:54:14.163Z
Answers.LastActivityDate: 2024-09-06T17:54:14.163Z
Answers.UserDisplayName: Coleman
Body: <p>I created a search with SearchKit, which has a lot of fields. On the Search Builder page (<code>/civicrm/admin/search#/edit/</code>), on the &quot;Select fields&quot; tab on there fields is summarised (&quot;Total paid&quot;), two of them is listed, and from one of them I pick the first value (&quot;Promised&quot;).</p>
  <p>Is it possible to filter the results based on the calculated difference of the &quot;Total paid&quot; and the &quot;Promised&quot; fields? I'd like to show in the result table only these rows, where the difference is bigger than zero.</p>
  <p>(I've tried to use the &quot;Total amount&quot; filed, but it causes SQL error.)</p>
  
Tags: <searchkit>
Title: Calculated field in SearchKit result
ThreadText: # Calculated field in SearchKit result
  
  (Asked by: username_0 on 2024-09-06)
  
  I created a search with SearchKit, which has a lot of fields. On the Search Builder page (`/civicrm/admin/search#/edit/`), on the "Select fields" tab on there fields is summarised ("Total paid"), two of them is listed, and from one of them I pick the first value ("Promised").
  
  Is it possible to filter the results based on the calculated difference of the "Total paid" and the "Promised" fields? I'd like to show in the result table only these rows, where the difference is bigger than zero.
  
  (I've tried to use the "Total amount" filed, but it causes SQL error.)
  
  **Tags:** searchkit
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2024-09-06. Score: 3
  
  Yes you can do field calculations in SearchKit and use them as filters
  
  1. **Field Calculation:** In the "Select Fields" section, click the little Fx button next to your field and select Math: Arithmetic. This will let you select another field and a comparison operator (in this case you want to subtract one field from the other).
  2. **Filtering:** Now that your calculation is done you can use the HAVING clause to filter results by the value of the calculated column.
  
  > **Comments:**
  > **username_0**: Thank you. For me, under the "fx" is "Math: Aritmetic" available only for some fields, like Contact ID (which type is number), but not for the fields, which type are eg. currency. And I really don't now, if it is a bug, a feature, a development request or my failure.  
  > **username_1**: Is "Promised" a custom field? If so you should make it a numeric field instead of a text field.  
  > **username_0**: No, "Promised" is the CiviCRM built-in Pledge's "Amount" field. This is stored in the database a number with currency. (Sorry, I user CiviCRM in Hungarian language, and sometimes it's wired and not quite easy to translate "back" the fields name to English.)  
  > **username_1**: Ok I'm not sure why it wouldn't work then. Arithmetic works on all numeric fields. What version of CiviCRM are you using?  
  > **username_0**: Continuously (since 2 years) the latest, now 5.77.0, with Drupal 7 (but I think, it doesn't matters).  
  > **username_1**: Could you try to reproduce the problem on https://dmaster.demo.civicrm.org/  
  > **username_0**: Yes. https://dmaster.demo.civicrm.org/civicrm/admin/search#/edit/47, on the "Select fields" tab, before the "Total amount" field, the available options are only the "Aggregate" options.  
  > **username_1**: I see, it's because when you add the `GROUP BY contact id` it means the columns have to be aggregated because you will have more than one contribution per row.  
  
  ---
  
  