1 2 Previous Next 21 Replies Latest reply on Oct 27, 2013 7:19 AM by Shawn Wallwork

    Number Formatting For Each Cell

    scott scottson

      Hey Guys,

       

      If I have a datable or Text Table, is it possible to format each individual Cell?

      Here is an example of what I mean (dummy data).

       

      CriteriaPercentage  IncreaseActual NumberPenalty Incurred
      Overdue Calls0.120£4,053
      Closed Calls0.2440£300
      Legacy Calls0.044£1,231
      Revenue Income0.1510000£120

       

       

      So Percentage Increase column should always be formatted as %, that's simple enough.

      The Actual Number column needs to be just plain numbers for Rows 1,2 and 3 but could do with being £10,000 for Revenue Income (Row 4). Is this possible?

       

      Thanks

      Scott

        • 1. Re: Number Formatting For Each Cell
          Aakash Jain

          Hi Scott,

          Check the attached workbook and let me know if it answers your query. I've used a caculated field 'Formatted Profit' to compute the profit in the desired format.

          Also to change the default format of your percent increase field you can change its default format properties to select percentage to always show it in percentage format.

          Hope it helps

           

          Regards,

          Aakash Jain

          1 of 1 people found this helpful
          • 2. Re: Number Formatting For Each Cell
            Prashant Sharma

            Hi Scott,

            You can also use Index with last to change the format to the last column. Using Lookup is another solution for this. But if are working on this same data, Aakash's solution will work for you.

             

            Warm Regards,

            Prashant Sharma - India | LinkedIn

            1 of 1 people found this helpful
            • 3. Re: Number Formatting For Each Cell
              scott scottson

              Guys,

               

              Thank you both for your help.

              It seems to work, the only question I have is can it actually be formatted as a currency or does it have to be string manipulation?

              The reason I ask is it look like £100000.00 ideally it would be £100,000.00 I can't just put a comma at the 4tgh character as it might me 10,000?

               

              Do I have to do something overly complex like checking if the value is above 999 then put a comma as the 2nd character, if it is above 9999 put a comma as the 3rd character etc?

              • 4. Re: Number Formatting For Each Cell
                Aakash Jain

                Hi Scott,

                Glad it was helpful for you. For your comma-separator query, I am afraid you'll have to use string manipulation in the calculated field itself, as tableau do not provides the feature to format a string like that by default. And yes the calculated field would include the similar case scenarios you mentioned.

                 

                 

                Regards,

                Aakash Jain

                • 5. Re: Re: Number Formatting For Each Cell
                  Jonathan Drummey

                  If the Actual Number column will always be positive, there's a solution using a calculated field and the conditional formatting for negative numbers:

                   

                  Screen Shot 2013-10-25 at 1.52.18 AM.PNG.png

                   

                  Here's the formatting:

                   

                  Screen Shot 2013-10-25 at 1.52.31 AM.PNG.png

                   

                  And the calculation:

                  Screen Shot 2013-10-25 at 1.54.11 AM.PNG.png

                   

                  I set it up in the attached workbook.

                   

                  Hope this helps!

                   

                  Jonathan

                  • 6. Re: Number Formatting For Each Cell
                    scott scottson

                    Hi Jonathan,

                     

                    That looks like it could be the answer to my problems. I will check it out when I get home this evening.

                     

                    Thanks again guys, you've been a HUGE help!

                    • 7. Re: Number Formatting For Each Cell
                      scott scottson

                      Hey,

                       

                      I've been toying about and I got the very large custom field formatting to work, for anyone that's interested I've pasted what it looks like below.

                      The issue I have now is because this is a calculated field and it's resulting in a string output, I've lost the ability to do anything with it really, I can stick it in a table, but it has no tooltip properties?

                       

                      IF ATTR([CriteriaId])== 3 THEN

                          IF SUM([DataValue]) > 999999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),1) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),2,3) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),5,3)

                          ELSE IF SUM([DataValue]) > 99999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),3) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),4,3)

                          ELSE IF SUM([DataValue]) > 9999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),2) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),3,3)

                          ELSE IF SUM([DataValue]) > 999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),1) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),2,3)

                          ELSE IF SUM([DataValue]) <= 999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),3)

                          END

                          END

                          END

                          END

                          END

                      ELSE IF ATTR([CriteriaId])== 26 THEN

                          IF SUM([DataValue]) > 999999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),1) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),2,3) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),5,3)

                          ELSE IF SUM([DataValue]) > 99999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),3) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),4,3)

                          ELSE IF SUM([DataValue]) > 9999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),2) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),3,3)

                          ELSE IF SUM([DataValue]) > 999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),1) + ',' + MID(STR(ROUND(SUM([DataValue]),0)),2,3)

                          ELSE IF SUM([DataValue]) <= 999 THEN

                                  '£' + LEFT(STR(ROUND(SUM([DataValue]),0)),3)

                          END

                          END

                          END

                          END

                          END

                      ELSE

                          STR(SUM([DataValue])) + '%'

                      END

                      END


                      • 8. Re: Number Formatting For Each Cell
                        scott scottson

                        Hey, Thanks,

                         

                        I tried the formatting and it worked except my zeros went completely blank?

                        • 9. Re: Number Formatting For Each Cell
                          Shawn Wallwork

                          Try changing the custom formatting from

                           

                          #,#;$#,#

                          to

                          #,#0;$#,#0

                           

                          --Shawn

                          1 of 1 people found this helpful
                          • 10. Re: Number Formatting For Each Cell
                            scott scottson

                            Excellent,

                             

                            This is now working.

                            Final question that should keep me happy and quiet.

                             

                            Is there anyway to deal with negative numbers like this?

                            • 11. Re: Number Formatting For Each Cell
                              Shawn Wallwork

                              Not using this particular formatting trick. It's binary. If you can live without using an extract, there's a RAWSQL_STR solution to this. See attached.

                               

                              --Shawn

                              • 12. Re: Number Formatting For Each Cell
                                scott scottson

                                ok so i'm back to my very long winded calculated field trick.

                                 

                                My problem now is that (unless I am missing something really simple).

                                I can't give my Column header for it an Alias, as I have to put it into my Table as a Row and not as a Measure?

                                 

                                So I have my real row which is "Lowest", my calculated field can not be called Lowest I have to call it something else and then I can't give it an Alias? So i'm stuck with column headers all having _ after then like Lowest_ (to make them unique).

                                 

                                Grrrrrr frustration at myself

                                It feels like something that should be very simple but it is turning out to take a lot more of my time than it should be.

                                 

                                I would have torn my hair out already if it wasn't for you guys! Thanks Again!

                                • 13. Re: Number Formatting For Each Cell
                                  scott scottson

                                  Not having an extract means I can still play with it as much as I want and manipulate it but I can't send it on to other people?

                                  • 14. Re: Number Formatting For Each Cell
                                    Shawn Wallwork

                                    Not necessarily. What's your data source?

                                     

                                    --Shawn

                                    1 2 Previous Next