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

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

• ###### 1. Re: How to add thousand separators (commas) to an aggreagted string number calculation

Hi Ravit

You do not need to make the values to string to add the \$ prefix

Right click to format the number, you have currency option in the number format and you can set \$ as prefix and chose to have the thousand separators.

Right click the measure-> Default Properties -> Number format

J

• ###### 2. Re: How to add thousand separators (commas) to an aggreagted string number calculation

Hi Jyothisree,

Thanks for your reply. I understand what you are saying but that is not the intention. This calculation is a part of a bigger string and the max of bill amount needs to be converted to a string. The issue isn't really the "\$" sign as much as the thousand separators. This is where I am struggling a bit.

• ###### 3. Re: How to add thousand separators (commas) to an aggreagted string number calculation

Hey Ravit,

Can you please share faked .twbx file for better assistance.

• ###### 4. Re: How to add thousand separators (commas) to an aggreagted string number calculation

Hey Ravit,

try this.

IF LEN(STR(([Calculation4])))<=3 THEN STR([Calculation4])

ELSEif

LEN(STR(([Calculation4])))<=4 THEN

LEFT(STR(([Calculation4])),1)+','+RIGHT(STR(([Calculation4])),3)

ELSEif

LEN(STR(([Calculation4])))<=5 THEN

LEFT(STR(([Calculation4])),2)+','+RIGHT(STR(([Calculation4])),3)

ELSEif

LEN(STR(([Calculation4])))<=6 THEN

LEFT(STR(([Calculation4])),3)+','+RIGHT(STR(([Calculation4])),3)

ELSEif

LEN(STR(([Calculation4])))<=7 THEN

LEFT(STR(([Calculation4])),1)+','+LEFT(STR(([Calculation4])),3)+','+RIGHT(STR(([Calculation4])),3)

END

2 of 2 people found this helpful
• ###### 5. Re: How to add thousand separators (commas) to an aggreagted string number calculation

Hi Venkat,

Thanks for your response. What's the version of the attached tableau workbook? My version is unable to open it.

1 of 1 people found this helpful
• ###### 6. Re: How to add thousand separators (commas) to an aggreagted string number calculation

This is 10.4. Which version your working on..?

2 of 2 people found this helpful
• ###### 7. Re: How to add thousand separators (commas) to an aggreagted string number calculation

I am working on 10.3.2

1 of 1 people found this helpful
• ###### 8. Re: How to add thousand separators (commas) to an aggreagted string number calculation

FYI,

I do  down graded to 10.3. Try now and let me  know if you have any questions.

2 of 2 people found this helpful
• ###### 9. Re: How to add thousand separators (commas) to an aggreagted string number calculation

Calculation4:INT((ROUND(SUM([Sales]),0)))

2 of 2 people found this helpful
• ###### 10. Re: How to add thousand separators (commas) to an aggreagted string number calculation

thanks for your calculation. exactly what i needed.

however there is a small mistake in Calculation 5 at the end.

It should be like:

LEN(STR(([Calculation4])))<=7 THEN

LEFT(STR(([Calculation4])),1)+'.'+MID(str([Calculation4]),LEN(str([Calculation4]))-5,LEN(str([Calculation4]))-(LEN(str([Calculation4]))-3))+'.'+RIGHT(STR(([Calculation4])),3)

1 of 1 people found this helpful
• ###### 11. Re: How to add thousand separators (commas) to an aggreagted string number calculation

You Welcome Dominik.

• ###### 13. Re: How to add thousand separators (commas) to an aggreagted string number calculation

In case it helps anybody else out, I cobbled this together out of a variety of suggestions to deal with dynamic international currency measure formatting:

if countd([Country Nm]) = 1 then

min([Selected Currency Prefix])

+    if min([Selected Currency Prefix]) = "¥"

then regexp_replace(str(round([InternationalCurrencyMeasure]), 0)), "(\d)(?=(\d{3})+\$)","\$0,")

else regexp_replace(split(str(round([InternationalCurrencyMeasure]), 2)), ".", 1), "(\d)(?=(\d{3})+\$)","\$0,") + right(str(round([InternationalCurrencyMeasure]) - int([InternationalCurrencyMeasure])), 2)), 3)

end

else null

end

JPY is handled separately because no decimal places. And it puts NULL in totals rows because 5USD + 10EUR doesn't make any sense.