8 of 8 people found this helpful
There is currently no "FORMAT()" function in Tableau, and it would be really useful for your situation. Maybe if enough people request it, the developers will implement one for us. :)
Here is a bit of trivial for you:
If you write a formula like:
and the number is over a thousand, like "12,000" this will return "12000" dropping the thousands separator.
But is you use
"12,000" will be returned, with the thousands separator, irregardless of the formatting applied. This could be considered a bug, but I like to exploit it. :)
(side note, in the LOOKUP() function above, partitioning does not matter, because it is looking up the value zero rows away, which is to say the same value in partition. I believe the rest of the Table Calc functions or operations involving them will return the thousands separator as well)
So that is half the battle, and makes life easier when trying to roll your own formatting function and you want to display the thousands separator.
If you just want a simple no decimal place percentage,
But if you want some decimal places, then it can get hairy quickly, and I would recommend considering a different approach or waiting until the FORMAT function is implemented in Tableau.
(side note, in cases not dealing with table calculations or data blends, I have used
with success with MS Jet as the data source)
You can reference other calculated fields from another calculated field, and this allows those referenced calculated fields to have their own partitioning.
and that address your second question.
Attached is your workbook edited with these concepts in use.
(side note, I am creating tutorial videos on interesting concepts such as these and will likely make a video of this situation within the next week, and post a link in this thread)
Many thanks for this excellent response. I would have thought that row and column totals wouldn't be valid for string fields. Hopefully a FORMAT function will show up soon.
The need for this display approach arose from a crowded dashboard. There just wasn't enough room to display all three types of tables side-by-side. Being able to provide the users with this type of flexibility for various components of the dashboard opens up many new possibilities.
I'm looking forward to the tutorial video. The ones I've seen so far have been a big help!
1 of 1 people found this helpful
I'm hearing from this thread a +1 for a FORMAT() function, +1 for "parameters as field pickers" (with their appropriate formatting), and a bug report on STR(LOOKUP()). :-)
1 of 1 people found this helpful
Yes, but don't fix the STR(LOOKUP()) bug until you add a FORMAT() function. I'm always banging my head against the wall when putting numbers in tooltips and titles. FORMAT() can't come quick enough!
James, notice in my side note, I mention that the thousands separator is added when any table calculation is performed, and that I used LOOKUP(,0) just because it was the easiest table calc function in my mind that references the current value in the partition.
So in other words, if Tableau can format numbers returned from table calc operations, I don't expect that table calcs being able to respect a field's default number format or a format function would be far away.
if you listen carefully I think you can hear + quite a few, not just +1, James!
Add another vote from a developer who really</> wants FORMAT() as well !!! This should have been an original function since the beginning.
I vote for the Format function as well. We are also experiencing over crowded dashboards.
One more caveat on this:
where [Number] is a *parameter* will not drop the thousands separator. However, if you create a calculated field based on the Parameter and use that, it will drop the thousands separator.
Does anyone know whether this FORMAT() function is likely to put in an appearance in the upcoming version 6.1?
It's certainly not in the beta and I've heard no mention of it.
That's a great shame. Parameters are indispensible when it comes to making simple mutli-funtional charts but without this facility their use can never be completely professional.
Whew, it's a good thing Tableau has parameters. I'd hate to think of this application as only 9/10ths professional.