12 Replies Latest reply on Mar 6, 2017 8:51 AM by Joe Oppelt

Formula to create diverging color for line

Hey ya'll - I'm needing to create a colored bar from red - blue - green that starts at 0 (red) and goes to 100 (green).  This is on a dual axis with another value that represents an image on that line relative to the value.  For example the value is 55, an image will show on the line at 55.

I have a simple calculation for 100 that i'm using to draw the line for the second axis, but i need it to go from red-blue-green.  There isn't any other value that related to making this line, so it's just a formula ... but i'm sure there is a better way to have this.

Something like the image below ...

John

• 1. Re: Formula to create diverging color for line

If you put the 0-100 calc field on the COLOR shelf, it will let you pick from the various diverging color schemes that Tableau offers, but there isn't a red-blue-green option available.  (There is a red-WHITE-green one though.)

Tableau will put the minimum value in your data set at the red end, and the maximum value at the green end.  If none of your rows calculate a minimum of sero, Tableau will use the minimum value by default, but you can override that and force zero at the low end and 100 at the high end.

• 2. Re: Formula to create diverging color for line

You will need to generate a custom color palette.

I'd use this website to generate a good color list: http://colllor.com/

For instance here is Red to Blue:

http://colllor.com/FF2424-266eff

And Blue to Green:

http://colllor.com/266EFF-26ff72

You can combine those into a custom color palette by editing the Preferences.TPS in

Do it! here's the color palette that works.  You can add in as many colors between the three as you'd like

<color-palette name="Red_Blu_Grn" type="ordered-diverging">

<color>#26ff72</color>

<color>#266EFF</color>

<color>#FF2424</color>

</color-palette>

• 3. Re: Formula to create diverging color for line

Wow.

I have never messed with this before.

So do I open the TPS file with Notepad and insert the COLOR xml stuff between

<workbook>

</workbook>

in that file?

• 4. Re: Formula to create diverging color for line

In between the

<preferences>

</preferences>

tags.  Here's what it looks like for me:

Again the user may want to select a more pleasing red-blue-green combination.  The Purple seems heavy.

• 5. Re: Formula to create diverging color for line

Hey Joe - the formula value for the bar will contain a single number, like 100.  this will make the bar stretch from 0 to 100.  when adding this to the color shelf, the only color - even with a divergent color - will be the green color, even when setting the start/ending from 0 and 100.

Am I missing something?

• 6. Re: Formula to create diverging color for line

The (bar 10) is a calculated field that is equal to 100

• 7. Re: Formula to create diverging color for line

Actually John,

It sounds like you might want a background image.  That could be easier for your viz.  The user doesn't need to interact with that background bar right?

• 8. Re: Formula to create diverging color for line

That's correct - they won't interact with the colored bar ... the large circle will move up and down the bar - like a slider.

A background probably would work better ... do you have a link to somewhere I can find a walkthrough on adding the background?

• 9. Re: Formula to create diverging color for line

So like this?

I had some difficulty sizing this correctly (it was giant when I first loaded it).  Let me know if you need additional help.

First create a custom calculated field called 10 and use the formula Max(10).

Then go to Map => Background Images  => Datasource

X Field: [Metric you want to use] for Left: 0 Right:100

Y Field:  [10] Bottom: 0 Top: 20

=> OK

Image File:

Red_Blu_Green_100.png  20x100

Workbook:

SuperStore.twbx

1 of 1 people found this helpful
• 10. Re: Formula to create diverging color for line

Thanks Kris ... still working on the formatting some, but for the most part I got it working.  When I added it to my dashboard, the x-axis adjusted to like -60 to 140 ... but that appeared when i set the sheet to 'fit entire view'

This has gotten me further along in my quest - thanks for the help !

John

• 11. Re: Formula to create diverging color for line

Are there any other ways to do this that don't involve using a background image?

As part of a dashboard I'm looking to make a few of these that update depending on filters!

example

1     A     88        100

2     A     95        100

3     A     60        100

4     B     55     100

5     B     75     100

6     C     92     100

So it will show 6 Lines red to green with the score and target.  if you filter to A it adjusts to 3 lines, B to 2 lines etc.

I managed to make this using the instructions above but that is based on 1 result. I have around 150 coming under 20-25 categories so I would like to be able to filter on the categories!

Any help would be appreciated

• 12. Re: Formula to create diverging color for line

I recommend that you start a new thread Matthew.  you can refer to this one.

The only people who will ever see this are people who participated in the old discussion.  You really limit the number of people who can help you by tacking onto an old closed thread.