10 Replies Latest reply on Mar 11, 2016 5:34 AM by Sreehari Katageri

    Conditional Number Formatting

    Brian Hannah

      Hello all,

       

      Is it possible to change the number of digits after a decimal point based on the value. Essentially, if the result is <100 I want a decimal point, if it is >100 I don't want one. I know I could do this with different sheets and a parameter, but that really isn't feasible because there are multiple metrics on the sheet all independent of each other.

       

      I don't think there is a way to do this, but maybe someone has an idea!

       

      Thanks

        • 1. Re: Conditional Number Formatting
          Kevin Eberhardt

          Hi Brian,

          maybe you try this:

           

          IF([Profit] < 100)

          THEN REPLACE(str(ROUND([*YOUR-VALUE*],1)),".",",")

          ELSE

          REPLACE(str(ROUND([*YOUR-VALUE*],0)),".",",")

          END

           

           

          Sincerely,

          Kevin

          • 2. Re: Conditional Number Formatting
            pooja.gandhi

            Hi Brian!

             

            You can create a calc like:

             

            Calculation 1: if sum([Profit]) > 100 then 'More' elseif sum([Profit]) < 100 then 'Less' end

            Calculation 2: if [Calculation1] = 'More' then sum([Profit]) elseif [Calculation1] = 'Less' then -sum([Profit]) end

             

            Notice the (minus) sign in the elseif condition. This will allow you to custom format negative numbers in a different way. Then right click on 2nd calc int he measure pane choose default properties > number format > custom. Enter this in the custom format box:

             

             

            So glad I got to use a nice trick taught to me by my fellow Ambassador Shawn Wallwork

            2 of 2 people found this helpful
            • 3. Re: Conditional Number Formatting
              pooja.gandhi

              OR actually you can just create 1 calculated field:

               

              if sum([Profit]) > 100 then sum([Profit]) elseif sum([Profit]) < 100 then -sum([Profit]) end

               

              Then right click on this calculated field and use the custom formatting I explained above

              1 of 1 people found this helpful
              • 4. Re: Conditional Number Formatting
                Brian Hannah

                Thanks Pooja, this is an interesting solution, but it doesn't work if negative values are a legitimate value?

                 

                In my example I am dealing with growth rates, which can be positive or negative so this solution essentially turns the negative values positive in addition to removing the zeros.

                • 5. Re: Conditional Number Formatting
                  Brian Hannah

                  Thanks Kevin, that would work for single values, but then you remove the ability roll up the data because they are strings.

                  • 6. Re: Conditional Number Formatting
                    pooja.gandhi

                    Oh I see. You can change the formula to accommodate negative values in that case:

                     

                    if sum([Profit]) > 100 then sum([Profit]) elseif sum([Profit]) < 100 then -(-sum([Profit])) end

                     

                    In the custom formatting then, have it display the negative signs also:

                     

                     

                    Now you will notice that the negative values are retained as in the original profit measure with 2 decimal points. Does that work?

                    1 of 1 people found this helpful
                    • 7. Re: Conditional Number Formatting
                      Brian Hannah

                      So that fixes the negative issue, but based on your output it looks like it is only working for the negative values now. It would need to work for both negative and positive values.

                       

                      Results should look like:

                      65.33

                      -23.55

                      1000

                      -144

                      • 8. Re: Conditional Number Formatting
                        pooja.gandhi

                        Oh yes, that's right! You have a mixed condition for positive numbers (some need decimals and some do not based on if they are greater than 100 or lowers than a 100). Our custom formatting took care of 2 settings (negative or positive). So all positives will not get a decimals and all negatives do but because you need some positives to get a decimal, I think converting the numbers to a string would be your only alternative at this point. Are you just displaying these numbers somewhere on the view or actually using them for like grand totals etc?

                         

                        Discrete values can not be used for grand totals, so if you convert the numbers to text, that might be an issue. Also which data source are you connecting to? Which version of Tableau are you using? Are you connecting live or using a data extract?

                        1 of 1 people found this helpful
                        • 9. Re: Conditional Number Formatting
                          Brian Hannah

                          Yeah I think the string would be the only way, which doesn't exactly work in my case because I need them to be rolled up in totals, but it would work if they are displayed on their own.

                           

                          I am connecting to a local excel data source with 9.2 and pulling an extract.

                           

                          Thanks for your help!

                          • 10. Re: Conditional Number Formatting
                            Sreehari Katageri

                            Hi ,

                             

                            I want the following number formatting in custom number format

                             

                            if number is 10,000 then it should be 10,000

                            if number is 100,000 then it should be 1,00,000

                            if number is 1,000,000 then it should be 10,00,000 etc .....

                             

                            custom format should be dynamic like below excel formula

                             

                            [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0