
1. Re: User Filter/Parameter to Choose Rounding Format for numbers
Galen Busch Apr 20, 2017 8:46 AM (in response to Amritam Das)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 Apr 20, 2017 8:56 AM (in response to Amritam Das)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 Apr 20, 2017 9:04 AM (in response to Brian Dudley)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

Demo_rounding.twbx 1.2 MB


4. Re: User Filter/Parameter to Choose Rounding Format for numbers
Amritam Das Apr 20, 2017 9:06 AM (in response to Galen Busch)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 Apr 20, 2017 9:13 AM (in response to Galen Busch)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 Apr 20, 2017 9:14 AM (in response to Brian Dudley)@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 Apr 20, 2017 9:16 AM (in response to Amritam Das)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 Apr 20, 2017 9:25 AM (in response to Justin Larson)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 Apr 20, 2017 9:30 AM (in response to Brian Dudley)@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 Apr 20, 2017 9:35 AM (in response to Justin Larson)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

Demo_rounding.twbx 1.2 MB


11. Re: User Filter/Parameter to Choose Rounding Format for numbers
Brian Dudley Apr 20, 2017 9:38 AM (in response to Amritam Das)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 Apr 20, 2017 9:51 AM (in response to Amritam Das)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 Apr 20, 2017 9:53 AM (in response to Amritam Das)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.