4 Replies Latest reply on Jul 28, 2016 6:52 AM by Mark Fraser

# Returning A String or Numeric Value from Calculation

We report p-values (what these are aren't really important to the post) for our reports to our clients. Currently, the p-value is a number calculated in Tableau that returns a numeric value out to 3 decimals places. But now our client wants something different. If the p-value is less than 0.001, they don't want the numeric value, but wants Tableau to return "< 0.001", a string value. I've included an image as an attachment that shows p-values for 3 separate data points. You'll notice that 2 are clearly above 0.001 and the last one is below (the value again has been rounded to 3 decimal places). So I'm at a bit of a loss. I tried writing a separate calculation using an IF statement and referencing the original P-value calculation:

• If [P-Value] < 0.001 then '< 0.001' else [P-Value] end (this was invalid)

I've also tried....

• If [P-value] < 0.001 then '<0.001' else STR([P-value]) end (this was valid, gave me a boolean expression)

Can anyone assist with this? In general, I want a calculation that can return either the string value (< 0.001) if the p-value meets the criteria (i.e. less than 0.001) and returns the original numeric value (the P-Value) if the criteria is not met.

-Cayla

• ###### 1. Re: Returning A String or Numeric Value from Calculation

Hi Cayla,

your second calculation looks fine to me?

Please see attached workbook where i have turned your numbers into a label.

• ###### 2. Re: Returning A String or Numeric Value from Calculation

Cayla

• If [P-Value] < 0.001 then '< 0.001' else [P-Value] end (this was invalid)

This was close, the problem is you're mixing the outputs

True = string

False = integer     hence invalid

What you could do... is use the STR() function

So...

IF [Test Measure] < 0.001 THEN '<0.001' ELSE STR([Test Measure]) END

This forces the False output to be a string

Bu then the trailing characters, you only wanted 3 dp, then we add the left() function

The problem still remains - now they are strings, we can add them, divide them, sum them etc.

You may wish to do this as a 2nd field, and display the numeric and string values side by side

Hope that helps

Cheers

Mark

• ###### 3. Re: Returning A String or Numeric Value from Calculation

Thanks Mark! That was an option I originally tried, but I forgot to mention something important in my original post. There are also other measures on the shelf and I need the P-Value calculation to be on the end of the table. Please see images below. You'll notice that when I put your calculation in the view (image labeled "P-value_trial"), it puts it in the front of the other measures (with no label, which is another issue as well). If you look at the other attached image (image labeled "P-value_preferredlook"), this is the layout that I'd like; with the P-value at the end with a label. It would be great if I could make this a numeric function that would be used as a true measure (i.e. included with the other measure names in the view). Is this possible somehow?

P-Value_trial:

P-Value_preferredlook:

• ###### 4. Re: Returning A String or Numeric Value from Calculation

Hi Cayla

You'll notice that when I put your calculation in the view (image labeled "P-value_trial"), it puts it in the front of the other measures (with no label, which is another issue as well).

The reason you cant just drop it in, is because Tableau won't let you mix numbers and strings, the string as you found is forced to the left.

There are also other measures on the shelf and I need the P-Value calculation to be on the end of the table. possible somehow?

We can hack something but it does have its own drawbacks... you mentioned labels, that's one.

I don't have your data, you'll have to trust me, but this is the technically same... Sales, profit then a text field (on the right!!! )

done in superstore, 9.3.5 copy attached.

The issue is the labels are at the bottom, and while you can still get column totals, you couldn't get row totals.

Interested to know how? There is a blog >> Data Psientist: My Favorite Tableau Trick For Work: MIN(1)

I'll quickly write the steps

Create new field, called 1, right click, default properties, aggregation - minimum

My fake one is - IF SUM([Sales]) < 50000 THEN '<50000' ELSE LEFT(STR(SUM([Sales])),5) END

In row, put a dimension (blue pill)

In column, put 1, repeat this step for each column you want, i have 3.

Click the first green pill - find a measure, place on text (make sure it is set to text, it defaults to bar)

Click min 2, repeat for the next column

and again for the 3rd

finally - rght click the axis

set these

and

Right click the axis again, click format

navigate as shown, click none

Done

When you have questions, I'll try and help.

Cheers

Mark