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  
  
  ---
  
  