14 Replies Latest reply on Aug 15, 2016 1:28 PM by Mark Fagan

Replace number with a string if threshold not met?

Would appreciate any direction on how I can show "TF" (too few) in a measure that is a number data type.

For example:

If the Measure  is less than 40 then display "TF" instead of 40.

Thank you for any direction on this.

-Mark

• 1. Re: Replace number with a string if threshold not met?

That would be something like: IF sum([Sales]) < 40 then 'TF' END

You can add an ELSE part to say IF > 40 then SUM([Sales]) but you will have to convert the ELSE part to string as well because you cant have different data types in a single IF statement. Hope this helps!

• 2. Re: Replace number with a string if threshold not met?

it can be done through calculated column but you have to cast your number (i.e 40 in your example) to string. That's because you want 'TF' in the value and numerical column can't have string in it.

• 3. Re: Replace number with a string if threshold not met?

One approach is to recode the TF's as zero with a calculated field i.e. IF SUM(Sales) < 40 then 0 else SUM(Sales) END.

Then apply a custom number format to your measure;

EDIT (further clarification); the custom number format follows the same convention as Excel so I find that documentation to be most useful.Create or delete a custom number format - Excel

The important piece from that document;

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order. <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

3 of 3 people found this helpful
• 4. Re: Replace number with a string if threshold not met?

You cannot have a field that acts BOTH as a Measure or a String depending on criteria.   I would suggest to convert you measure number as a string:  STR([measure value])  then you can employ this logic that you can use in a table like chart, but not any other types of charts other than labels.

• 5. Re: Replace number with a string if threshold not met?

That is smart, Tom!

• 6. Re: Replace number with a string if threshold not met?

Thank you everyone!  I went with the formatting approach this round but will try other recommendations also on next project.

-Mark

• 7. Re: Replace number with a string if threshold not met?

Hi Tom,

I am transitioning some reporting from Excel to Tableau...

Thank you for the previous reply and the format technique worked great for "TF" in the volume measure however,  I need to take this one step further to display <1% instead of TF (too few) if the percentage in a different measure is between >0 and <.01

From what I have found online I cannot do a "TF" and a "<1%" as a custom format at the same time.

This is the formula in Excel to show "TF" (based on volume) or "<1%" based on percentage in another cell:

=IF(volume<40,"TF",IF(AND((percentage)>0,(percentage)<0.01),0.005,ROUND(percentage,2)))

So my column results in the  view could look like this:

8%

<1%

11%

TF

TF

4%

<1%

Any assistance would be very appreciated.

Thanks,

-Mark

• 8. Re: Replace number with a string if threshold not met?

My trick is only going to work in one scenario, and that scenario is where the row = 0. Thus, it's not possible to use the same approach for two different scenarios.

The only other way to do this is to output the field as a string as mentioned by someone here earlier. There are limitations to this approach i.e. you won't be able to visualize this field using a bar or column.

IF Sales < 40 then "TF"

ELSEIF PERCENTAGE > 0 AND PERCENTAGE <0.001 THEN "<1%"

ELSE STR(Sales).

• 9. Re: Replace number with a string if threshold not met?

Not sure what is happening but I cannot figure this out.  Here is the calculated field.  Is there anything that looks incorrect?

if [Sales] <40 then ”TF” elseif  [Percentage] >0 and [Percentage] <0.001 THEN “<1%” ELSE STR([Sales]) END

Calculation is valid but the calculated field pill in the Marks section changes from green to red and the data in the worksheet is grayed out.

Thank you for any assistance.

-Mark

• 10. Re: Replace number with a string if threshold not met?

It's likely that you're trying to use it as an aggregate or a measure, and that won't work because it's a string.

Remove the pill from the sheet entirely before making the changes, then re-add it and see if you get the desired outcome.

• 11. Re: Replace number with a string if threshold not met?

The results do not appear to be working out.   Not sure what else to  change but thank you for trying.  I really appreciate it!

-Mark

• 12. Re: Replace number with a string if threshold not met?

Sometimes it is possible to combine two or more calculations as shown here:

Re: Custom Number Formatting (\$K,\$M)

Custom Number Format

• 13. Re: Replace number with a string if threshold not met?

Hi Mark,

If you would like me to look further please upload a Tableau Packaged Workbook with the calculation you've created along with a description of what isn't working.

• 14. Re: Replace number with a string if threshold not met?

Thanks Tom,  I will work on a test dataset as soon as I can.

-Mark