Forum Discussion

StephenJackson's avatar
StephenJackson
Qrew Member
3 years ago

Commas added to currency numeric field summaries

In a Summary Report, I am trying to make the 'Report formula' columns display with a comma added every three digits. ex: $1,234,321.00 instead of $1234321.00 The 'Report formula' is summarizing a N...
Formulas and functions
reports and charts
MattHardy's avatar
MattHardy
Qrew Trainee
Hi Stephen,

Try the code below, it's something I found on here for a problem I had awhile ago. A quick test seemed to function properly as a report formula.

var text subtotal = ToFormattedText(Round( [Number - currency field]],0.01),"comma_dot");
var number subwheredot=If(Contains($subtotal,"."),Length(Right($subtotal,".")),0);
var text subtotaldisplay = If($subwheredot = 0, $subtotal & ".00", If($subwheredot = 1, $subtotal & "0", $subtotal));

$subtotaldisplay

------------------------------
Matt Hardy
------------------------------
StephenJackson's avatar
StephenJackson
Qrew Member
3 years ago
Hi Matt,

Thanks for your help!!

Your solution is still gives me an error where it says "Expecting number but found text" if the Report formula is a Formula - Numeric. 

Or if I change it to Formula - Text, it'll only summarize by Distinct Count in the Summarize Data section. 

Any ideas?

It

------------------------------
Stephen Jackson
------------------------------
  • MattHardy's avatar
    MattHardy
    Qrew Trainee
    3 years ago
    Unfortunately, I misunderstood your original question and can't offer any more suggestions. Hopefully, someone else comes along with an answer for you.

    ------------------------------
    Matt Hardy
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew Champion
      3 years ago
      Stephen,
      Did you try simply setting the format of your Summary Formula

      Step 2: Write summary formulas

      Use your variables as you would normally use fields. For example, an Income formula might be "[Revenue (tot)] - [Expense (tot)]".

      Summary Formula 1  Numeric - Currency


      [Invoice Amount (tot)] / [Number of Time Records]


      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------