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

    Returning A String or Numeric Value from Calculation

    Cayla Wigfall

      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.

       

      Please help!

       

      -Cayla

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

          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
            Mark Fraser

            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
              Cayla Wigfall

              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
                Mark Fraser

                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

                Create your p value field

                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