Answers.Body: <p>I was finally able to use Smarty to rewrite a random field to be the &quot;Amount Left&quot;, but it's not pretty:</p>
  <pre><code>{capture name=goal assign=goal}[goal_revenue]{/capture}
  {capture name=goal2 assign=goal}{$goal|replace:&quot;$&quot;:&quot;&quot;|replace:&quot;,&quot;:&quot;&quot;}{/capture}
  {capture name=raised assign=raised}[SUM_Campaign_Contribution_campaign_id_01_total_amount]{/capture}
  {capture name=raised2 assign=raised}{$raised|replace:&quot;$&quot;:&quot;&quot;|replace:&quot;,&quot;:&quot;&quot;}{/capture}
  {capture name=remaining assign=remaining}{math equation=&quot;x - y&quot; x=$goal y=$raised}{/capture}
  {$remaining|crmMoney}
  </code></pre>
  <p>It seems like it would be easier if any of the following were true:</p>
  <ul>
  <li>A &quot;raw&quot; Smarty filter were available to pass my currency as a number.</li>
  <li>It's possible to use Smarty filters directly on SK tokens.</li>
  <li>It's possible to use arithmetic SQL transformations with aggregated values.</li>
  <li>A field transformation &quot;Raw&quot; existed to disable formatting.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-18T19:29:57.437Z
Answers.Text: I've tried to make raw values happen but haven't got it past review. https://github.com/civicrm/civicrm-core/pull/26742
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-08-15T20:42:40.640Z
Answers.LastActivityDate: 2023-08-15T20:42:40.640Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>I'm trying to replicate a (buggy) report in Extended Reports, basically:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Campaign</th>
  <th>Goal</th>
  <th>Amount Raised</th>
  <th>Amount Left</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Capital Campaign</td>
  <td>$10,000</td>
  <td>$8,000</td>
  <td>$2,000</td>
  </tr>
  <tr>
  <td>Another Campaign</td>
  <td>$15,000</td>
  <td>$6,000</td>
  <td>$9,000</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The equivalent SQL would be:</p>
  <pre><code>SELECT campaign.title as &quot;Campaign&quot;, campaign.goal_revenue AS &quot;Goal&quot;, SUM(contrib.total_amount) AS &quot;Amount Raised&quot;, goal_revenue - SUM(contrib.total_amount) as &quot;Amount Left&quot;
  FROM civicrm_campaign campaign
  JOIN civicrm_contribution contrib ON campaign.id = contrib.campaign_id
  GROUP BY campaign.id;
  </code></pre>
  <p>However, there seems to be no way to do this.  SQL transformations won't work because I can't subtract the aggregated Amount Raised from Goal (or do arithmetic transforms on aggregated fields).  But Smarty treats the values as strings, with no way to convert to numbers (using <code>intval</code> filter or casting to <code>(int)</code>) both generate errors.</p>
  
Tags: <searchkit><smarty><currency>
Title: SearchKit: How can I do math on currencies?
ThreadText: # SearchKit: How can I do math on currencies?
  
  (Asked by: username_0 on 2023-08-15)
  
  I'm trying to replicate a (buggy) report in Extended Reports, basically:
  
  | Campaign | Goal | Amount Raised | Amount Left |
  | --- | --- | --- | --- |
  | Capital Campaign | $10,000 | $8,000 | $2,000 |
  | Another Campaign | $15,000 | $6,000 | $9,000 |
  
  The equivalent SQL would be:
  
  ```
  SELECT campaign.title as "Campaign", campaign.goal_revenue AS "Goal", SUM(contrib.total_amount) AS "Amount Raised", goal_revenue - SUM(contrib.total_amount) as "Amount Left"
  FROM civicrm_campaign campaign
  JOIN civicrm_contribution contrib ON campaign.id = contrib.campaign_id
  GROUP BY campaign.id;
  
  ```
  
  However, there seems to be no way to do this. SQL transformations won't work because I can't subtract the aggregated Amount Raised from Goal (or do arithmetic transforms on aggregated fields). But Smarty treats the values as strings, with no way to convert to numbers (using `intval` filter or casting to `(int)`) both generate errors.
  
  **Tags:** searchkit, smarty, currency
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2023-08-15. Score: 2
  
  I was finally able to use Smarty to rewrite a random field to be the "Amount Left", but it's not pretty:
  
  ```
  {capture name=goal assign=goal}[goal_revenue]{/capture}
  {capture name=goal2 assign=goal}{$goal|replace:"$":""|replace:",":""}{/capture}
  {capture name=raised assign=raised}[SUM_Campaign_Contribution_campaign_id_01_total_amount]{/capture}
  {capture name=raised2 assign=raised}{$raised|replace:"$":""|replace:",":""}{/capture}
  {capture name=remaining assign=remaining}{math equation="x - y" x=$goal y=$raised}{/capture}
  {$remaining|crmMoney}
  
  ```
  
  It seems like it would be easier if any of the following were true:
  
  * A "raw" Smarty filter were available to pass my currency as a number.
  * It's possible to use Smarty filters directly on SK tokens.
  * It's possible to use arithmetic SQL transformations with aggregated values.
  * A field transformation "Raw" existed to disable formatting.
  
  > **Comments:**
  > **username_1**: I've tried to make raw values happen but haven't got it past review. https://github.com/civicrm/civicrm-core/pull/26742  
  
  ---
  
  