1 2 Previous Next 29 Replies Latest reply on Apr 17, 2017 1:45 PM by Tonia Achison

# Can Formatting be Changed Based on a Parameter?

I have a basic worksheet (see attached) that has Country on the Columns shelf and Age on the Rows shelf.  I use a parameter to enable the user to display either the actual number of incidents for each age/country or the age's percent of the total number of incidents for the country.  This parameter controls the way the measure 'Display Data' is calculated.  While the calculated percentages are correct, they are not formatted as percentages: thus, instead of 6.1% the user sees 0.061.

Is there any way to dynamically change the formatting based on the parameter value selected so that the formatting is appropriate?

Also, is there any way in a calculated field to specify the scope of the calculation?  When I set up the worksheet I had to select Compute Using Table (Down) from the Text shelf's pill for the measure 'Displayed Data'.  I'd like to set up a second percentage calculation, selectable in the Parameter list, that would calculate the percentages based on Compute Using Table (Across).  That would require incorporating the 'Compute Using' information into the formula for the calculated 'Displayed Data' measure, and I don't know if that is possible.

• ###### 1. Re: Can Formatting be Changed Based on a Parameter?

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:

`STR([Number])`

and the number is over a thousand, like "12,000" this will return "12000" dropping the thousands separator.

But is you use

`STR(LOOKUP([Number],0))`

"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,

`STR(INT(ROUND([Number]*100,0)))+"%"`

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

`RAWSQL_STR("FORMAT(%1,'percent')",[Number])`

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.

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)

8 of 8 people found this helpful
• ###### 2. Re: Can Formatting be Changed Based on a Parameter?

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!

• ###### 3. Re: Can Formatting be Changed Based on a Parameter?

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
• ###### 4. Re: Can Formatting be Changed Based on a Parameter?

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!

1 of 1 people found this helpful
• ###### 5. Re: Can Formatting be Changed Based on a Parameter?

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.

• ###### 6. Re: Can Formatting be Changed Based on a Parameter?

if you listen carefully I think you can hear + quite a few,  not just +1, James!

• ###### 7. Re: Can Formatting be Changed Based on a Parameter?

Tutorial video posted: http://goo.gl/eyREX

• ###### 8. Re: Can Formatting be Changed Based on a Parameter?

Add another vote from a developer who really</> wants FORMAT() as well !!!  This should have been an original function since the beginning.

• ###### 9. Re: Can Formatting be Changed Based on a Parameter?

I vote for the Format function as well.  We are also experiencing over crowded dashboards.

• ###### 10. Re: Can Formatting be Changed Based on a Parameter?

One more caveat on this:

STR([Number])

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.

• ###### 11. Re: Can Formatting be Changed Based on a Parameter?

Does anyone know whether this FORMAT() function is likely to put in an appearance in the upcoming version 6.1?

• ###### 12. Re: Can Formatting be Changed Based on a Parameter?

It's certainly not in the beta and I've heard no mention of it.

• ###### 13. Re: Can Formatting be Changed Based on a Parameter?

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.

• ###### 14. Re: Can Formatting be Changed Based on a Parameter?

Whew, it's a good thing Tableau has parameters.  I'd hate to think of this application as only 9/10ths professional.

1 2 Previous Next