13 Replies Latest reply on Apr 20, 2017 9:53 AM by Justin Larson

    User Filter/Parameter to Choose Rounding Format for numbers

    Amritam Das

      Hi,

       

      Is there a way to use either a filter or parameter on a dashboard, using which the users can select the rounding of the numbers in the view to the thousand, million and billion (essentially being able to change the formatting of the numbers in the view)?

       

      Thanks,

      Amritam Das

        • 1. Re: User Filter/Parameter to Choose Rounding Format for numbers
          Galen Busch

          Hi Amritam,

           

          When changing the rounding like that, I recommend including the units in your calculation. e.g. (k) (m) (b) thousands/millions/billions. In that case, let's tackle it as a string.

           

          STR([Measure]) will convert an integer to a string.

           

          I'll use a parameter with the values 'Thousands' 'Millions' and 'Billions'

           

          If [Parameter] = 'Thousands' then (if LEN(STR([Measure])) > 3 then LEFT(STR([Measure]),3) + '(k)'

          Else STR([Measure]) end)

           

          Elseif [Parameter] = 'Millions' and LEN(STR([Measure])) > 6 then (

          LEFT(STR([Measure]),3) + '(m)'

          Elseif [Parameter] = 'Millions' then (if LEN(STR([Measure])) > 3 then LEFT(STR([Measure]),3) + '(k)'

          Else STR([Measure]) end)

           

          Elseif [Parameter] = 'Billions' and LEN(STR([Measure])) > 9 then (

          LEFT(STR([Measure]),3) + '(b)'

          ELSEIF [Parameter] = 'Billions' and LEN(STR([Measure])) > 6 then (

          LEFT(STR([Measure]),3) + '(m)'

          Elseif [Parameter] = 'Millions' then (if LEN(STR([Measure])) > 3 then LEFT(STR([Measure]),3) + '(k)'

          Else STR([Measure]) end)

           

          This is changing the aggregation depending on the number. If you have a 3 digit number, would you rather display it as .00 billion or $583?

           

          Alternative: use the round function with a

          CASE [Parameter]

           

          When 'Thousands' then str(ROUND([Measure],3)) + 'k'

           

          Etc.

           

          Galen

          • 2. Re: User Filter/Parameter to Choose Rounding Format for numbers
            Brian Dudley

            Rounding is straight forward. Add a parameter to indicate the number of digits and use that in a calculated field. I don't think that Tableau has any way to control formatting programatically. It could be done with enough string manipulation, but it would be hard to get right.

             

             

            Use Round(<expression>, [RoundTo])

            • 3. Re: User Filter/Parameter to Choose Rounding Format for numbers
              Amritam Das

              Hi Brian,

               

              Thank you for your prompt resppnse. Attached is a packaged Tableau workbook where I have tried to incorporate your suggestion. However, the numbers using the Round function and parameter seem to be different than the original. Could you please take a quick look?

               

              Thanks,

              Amritam

              • 4. Re: User Filter/Parameter to Choose Rounding Format for numbers
                Amritam Das

                Hi Galen,

                 

                Thank you for your response. The problem with using this approach is it starts to treat the numbers as string, but I still want them to be numbers.

                 

                Thanks,

                Amritam

                • 5. Re: User Filter/Parameter to Choose Rounding Format for numbers
                  Justin Larson

                  edit on comment that changes measure to string. This is a separate calculation that you would only for your mark labels. The Measures themselves would not use this calculation.

                   

                  Agreed on all. The only addition I would make to this is that with parameters you can use values, but assign them labels for the control, like :

                   

                  by doing this, the calculations are simplifed in that you don't need to identify the string of the parameter then handle it, you can just evaluate it directly:

                   

                  //first part handles number

                  if LEN(STR([Measure])) > [Parameter] then LEFT(STR([Measure]),[Parameter]) + '(k)'

                  Else STR([Measure])

                  end

                   

                  //second part handles suffix

                  + if LEN(STR([Measure])) > [Parameter] then

                      case [Parameter]

                      when 3 then '(k)'

                      when 6 then '(m)'

                      ...

                      end

                  end

                  • 6. Re: User Filter/Parameter to Choose Rounding Format for numbers
                    Justin Larson

                    @Brian Dudley This was my first thought too. But all it does is cause a label that used to show 10,433 to now show 10,000. Doesn't really fix the formatting issue.

                    • 7. Re: User Filter/Parameter to Choose Rounding Format for numbers
                      Brian Dudley

                      The aggregation has to be included in the calculated field with the rounding. Otherwise, it is rounding each individual number and then summing.

                      • 8. Re: User Filter/Parameter to Choose Rounding Format for numbers
                        Brian Dudley

                        Seems like Tableau has most of the pieces in place to allow a true change of formatting via a parameter. Maybe in a future release.

                         

                        You are correct that it only addresses the rounding. That would take more string manipulation and would result in a string value.

                         

                        The rounding should probably be the first step before applying the additional formatting.

                        • 9. Re: User Filter/Parameter to Choose Rounding Format for numbers
                          Justin Larson

                          @Brian Dudley. Agreed - next time I'm on the horn with Tableau developers, I think I'm going to ask if any of them have ever used SSRS. Being able to calculate a result and assign that result to practically any property in the whole canvas is quite valuable for flexibility.

                          • 10. Re: User Filter/Parameter to Choose Rounding Format for numbers
                            Amritam Das

                            Hi Justin,

                             

                            I am having a little hard time implementing the logic probably because I am a newbie. Please refer to the sheet 2 in the attached packaged workbook. Could you help me understand what I am doing wrong here?

                             

                            Thanks,

                            Amritam

                            • 11. Re: User Filter/Parameter to Choose Rounding Format for numbers
                              Brian Dudley

                              Here's another thought.

                               

                              ROUND(Sum([Sales]),[RoundTo]) / Power(10, If [RoundTo] < 0 Then -[RoundTo] Else 0 End)

                               

                              This rounds then scales.

                               

                              Then a Unit column can be added:

                               

                              Case [RoundTo]

                                  When -3 Then 'K'

                                  When -6 Then 'M'

                                  When -9 Then 'B'

                              Else ''

                              End

                               

                              This leaves you with numeric values so other calculations can be done.

                              • 12. Re: User Filter/Parameter to Choose Rounding Format for numbers
                                Brian Dudley

                                Same problem. You have to put the aggregation into the calculation.

                                 

                                If [Rounding Parameter] = 'Thousands' then (if LEN(STR(Sum([Sales]))) > 3 then LEFT(STR(Sum([Sales])),3) + '(k)'

                                Else STR(Sum([Sales])) end)

                                 

                                Elseif [Rounding Parameter] = 'Millions' and LEN(STR(Sum([Sales]))) > 6 then (

                                LEFT(STR(Sum([Sales])),3) + '(m)')

                                ELSEIF [Rounding Parameter] = 'Millions' then (if LEN(STR(Sum([Sales]))) > 3 then LEFT(STR(Sum([Sales])),3) + '(k)'

                                Else STR(Sum([Sales])) end)

                                 

                                Elseif [Rounding Parameter] = 'Billions' and LEN(STR(Sum([Sales]))) > 9 then (

                                LEFT(STR(Sum([Sales])),3) + '(b)')

                                ELSEIF [Rounding Parameter] = 'Billions' and LEN(STR(Sum([Sales]))) > 6 then (

                                LEFT(STR(Sum([Sales])),3) + '(m)')

                                Elseif [Rounding Parameter] = 'Millions' then (if LEN(STR(Sum([Sales]))) > 3 then LEFT(STR(Sum([Sales])),3) + '(k)'

                                Else STR(Sum([Sales])) end)

                                END

                                • 13. Re: User Filter/Parameter to Choose Rounding Format for numbers
                                  Justin Larson

                                  Ah!

                                   

                                  I was understanding the request to be the labels for marks on the sheet. Interpreting these as text and throwing them on the Axis will be problematic, because Tableau can't turn discrete values into a continuous axis.

                                   

                                  Before I go down the wrong path entirely, can you send a screen shot of the chart you have in mind with everything working EXCEPT the labels, and highlight or circle what you would want to be dynamic.