10 Replies Latest reply on Jan 17, 2018 4:15 PM by Chris McClellan

# Wrong result displayed?

Using this on a map and my map label is incorrectly showing "More than 100 Trillion Dollars" when the actual answer should be anywhere from \$4,149,509 to \$44,799,854 dependent on which of the 10 zip codes is showing data.

I then created a calculation that is returning 18 for each of the 10 zip codes mapped:

len(STR([Sum Dollars]))

And when I change the below in the 4th ELSEIF from 15 to 18 I get the following for a zip code that should be \$4,149,509:

\$414950,8.8,799,999,971

Every one of the 10 data points is like the above example with the first part of the result being the correct dollar amount but each has a totally different second part of the number.

CASE[Choose Dollars or Units]

WHEN "Dollars" THEN

IF LEN(STR([Sum Dollars]))<3 THEN

"\$" + STR([Sum Dollars])

ELSEIF LEN(STR([Sum Dollars]))<=6 THEN

"\$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-3)

+ "," + RIGHT(STR([Sum Dollars]), 3)

ELSEIF LEN(STR([Sum Dollars]))<=9 THEN

"\$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-6)

+ "," + MID(RIGHT(STR([Sum Dollars]), 6), 1, 3) + ","

+ RIGHT(STR([Sum Dollars]), 3)

ELSEIF LEN(STR([Sum Dollars]))<=12 THEN

"\$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-9)

+ "," + MID(RIGHT(STR([Sum Dollars]), 9), 1, 3)

+ "," + MID(RIGHT(STR([Sum Dollars]), 6), 1, 3) + ","

+ RIGHT(STR([Sum Dollars]), 3)

ELSEIF LEN(STR([Sum Dollars]))<=15 THEN

"\$" + LEFT(STR([Sum Dollars]), Len(STR([Sum Dollars]))-12)

+ "," + MID(RIGHT(STR([Sum Dollars]), 12), 1, 3)

+ "," + MID(RIGHT(STR([Sum Dollars]), 9), 1, 3)

+ "," + MID(RIGHT(STR([Sum Dollars]), 6), 1, 3) + ","

+ RIGHT(STR([Sum Dollars]), 3)

ELSE

"More than 100 Trillion Dollars"

END

WHEN "Units" THEN

IF LEN(STR([Sum Units]))<3 THEN

STR([Sum Units])

ELSEIF LEN(STR([Sum Units]))<=6 THEN

LEFT(STR([Sum Units]), Len(STR([Sum Units]))-3)

+ "," + RIGHT(STR([Sum Units]), 3)

ELSEIF LEN(STR([Sum Units]))<=9 THEN

LEFT(STR([Sum Units]), Len(STR([Sum Units]))-6)

+ "," + MID(RIGHT(STR([Sum Units]), 6), 1, 3) + ","

+ RIGHT(STR([Sum Units]), 3)

ELSEIF LEN(STR([Sum Units]))<=12 THEN

LEFT(STR([Sum Units]), Len(STR([Sum Units]))-9)

+ "," + MID(RIGHT(STR([Sum Units]), 9), 1, 3)

+ "," + MID(RIGHT(STR([Sum Units]), 6), 1, 3) + ","

+ RIGHT(STR([Sum Units]), 3)

ELSEIF LEN(STR([Sum Units]))<=15 THEN

LEFT(STR([Sum Units]), Len(STR([Sum Units]))-12)

+ "," + MID(RIGHT(STR([Sum Units]), 12), 1, 3)

+ "," + MID(RIGHT(STR([Sum Units]), 9), 1, 3)

+ "," + MID(RIGHT(STR([Sum Units]), 6), 1, 3) + ","

+ RIGHT(STR([Sum Units]), 3)

ELSE

"More than 100 Trillion Units"

END

END

• ###### 1. Re: Wrong result displayed?

Hi, Greg

A sample workbook will be helpful.

ZZ

• ###### 2. Re: Wrong result displayed?

Hi Zhouyi,

What is the best way to package up a workbook that has 30meg worth of data?

• ###### 3. Re: Wrong result displayed?

Or another angle ... what are you REALLY trying to do ?

• ###### 4. Re: Wrong result displayed?

Hi Chris,

I ultimately want to show either dollars or units with the map labels. It would also be great to shorten them for M or k as well.

• ###### 5. Re: Wrong result displayed?

Swapping between dollars & units is probably best done using a parameter.

Changing the units can be done in the formatting:

Change the Units dropdown to your desired setting.

• ###### 6. Re: Wrong result displayed?

I have the parameter in this map to swap back and forth. Now I just need to solve the formatting issue that I described.

• ###### 7. Re: Wrong result displayed?

You should use STR(ROUND([Sum Dollars],0)) everywhere in place of STR([Sum Dollars]).

1 of 1 people found this helpful
• ###### 8. Re: Wrong result displayed?

Now does anyone know how I can further format these to use M or k for millions or thousands since I am doing this as a string?

• ###### 9. Re: Wrong result displayed?

Thank You!

• ###### 10. Re: Wrong result displayed?

If you're doing it as strings you have to do it yourself, that's why it's much better to leave as numbers and let Tableau do the formatting for you.

Do you need the "more than 1 trillion" text when the number is too big ?