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