13 Replies Latest reply on Aug 27, 2018 9:31 PM by Christopher Jones

    How to add thousand separators (commas) to an aggreagted string number calculation

    Ravit Thapar

      Hi Tableau Community,

       

      I have a calculation that displays max of a bill amount converted to a string and concatenated to text (please see below).

      Calculation:- 'Bill amount: $' + STR(ROUND(MAX([bill_amount]), 2))

       

      Current outcome:- Bill amount: $1234567.89

      Expected outcome:- Bill amount: $1,234,567.89

       

      I tried using the below calculation but it gives me the error: "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions."

      (The if statement compares with 7 because a number like 123.45 has length 6 (or <7) and needs no commas)

       

      'Bill amount: $' +

      IF LEN(str(ROUND(MAX([Bill_Amount]),2))) < 7

      THEN str(ROUND(MAX([Bill_Amount]),2))

       

      ELSE LEFT(str(ROUND(MAX([Bill_Amount]),2)), LEN(str(ROUND(MAX([Bill_Amount]),2))) - 6) + "," +

          MID(str(ROUND(MAX([Bill_Amount]),2)), LEN(str(ROUND(MAX([Bill_Amount]),2))) - 5, LEN(str(ROUND(MAX([Bill_Amount]),2))) - (LEN(str(ROUND(MAX([Bill_Amount]),2))) - 6))

       

      END

       

      I also tried using REGEXP_REPLACE but that didn't work either.

       

      I'd appreciate any help on this to resolve the issue.

       

      Thank you so much.

       

      Regards,

      Ravit