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 Numeric - currency field as Formula - Numeric. The Numeric - currency field's properties is formatted to include these commas. The commas appear when I look at an individual record. But the 'Report formula' column does not display them on the Summary Report.

I have tried: ToFormattedText([Number - currency field], "comma_dot") but it receives an "expecting number" error. 

If add ToNumber at the beginning of that formula, it displays the value without the commas. 

Changing the 'Report formula' to a Formula - Text means I can't summarize the total. I can only count distinct values.

Any ideas as to how I can add the commas?

#Formulasandfunctions
#reportsandcharts
​​

------------------------------
Stephen Jackson
------------------------------
Formulas and functions
reports and charts

4 Replies

Sort By
  • MattHardy's avatar
    MattHardy
    Qrew Trainee
    3 years ago
    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
        ------------------------------