1 2 Previous Next 15 Replies Latest reply on Apr 10, 2012 6:19 AM by Chris Turnbull

# Don't want to Colour One Column on a Heat Map

All.

Please find attached a workbook that has 5 sessions of results.

GCSE - This is a Predicted % That I need to Measure AH1, 2, 3 and 4 against.  The difference being called The Residual.

The Greener the Cell the better the residual - The Bluer the Cell the worse the Residual. Most of that work how I want it to.

My Question is How do I leave the GCSE Predicted Cell White with no Colouring.

A side question is How would I Calculate the Difference between each of the AH1, 2, 3 and 4 against the GCSE if I didn't have a Predicted Grade Column already setup in the extract.

Thanks

Chris

• ###### 1. Re: Don't want to Colour One Column on a Heat Map

Hi Chris,

There are a couple of ways of geting the one column to be white. Either way, you create a calculated field that returns one value when the AH Session is GCSE, and another for the other sessions. For the first option, the calculated field just returns a number that is bigger than any residual, then you use a custom color palette to get the blue/green/white diverging palette - see http://kb.tableausoftware.com/articles/knowledgebase/creating-custom-color-palettes for details. This way the GCSE column has a color value that is big enough to get its own white while everything else has a blue or green color. I created one in the "continuous" worksheet, and the colors I picked were a little too dark so I had to crank up the transparency.

The second option is to use an IF statement to "bin" the values of the residual and create categorical colors, then you can assign colors to those. I did this for a few bins, you could do even more. Personally, I prefer this option because it can effectively show what the residual is in the color legend.

As for the difference calculation, you can create a table calc like the following:

[%A*-A]-LOOKUP([%A*-A],FIRST())

What it's doing is finding the value of %A*-A for the first row in the partition, and subtracting that from the current value of %A*-A. When this is in the view, set the calc to Compute Using AH Session. This makes the calc find the first value for %A*-A for each AH Session (the GCSE value) and then use that value, until there's a new Subject, at which point it restarts.

Jonathan

• ###### 2. Re: Don't want to Colour One Column on a Heat Map

Hi Jonathan,

Thanks for this - It is fanatstic and what's more I can follow it. I have been doing battle with it for a couple of days.

I just couldn't workout what the calculated fields should be.  I have been using a lot of your techniques from here http://community.tableau.com/thread/115822 to great affect and they have been so useful.

Another problem I have is the following:- I have created a dashboard where I have 3 Worksheets - One for A*-A%, one for A*-C% and A*-G% and I line them up and take of the Subject Headers for the 2nd and 3rd sheet.  I really want them all on one worksheet but cannot workout how to Partition the Data.  I think it should be partitioned by a Result Category (which would include A*-A%, A*-C% and  A*-G%) and then by AH Session.  How would I create that Result Category with the example above.

I really do appreciate both this help and the other what habe turned out to be learning resourceson the board they have been invaluable to my Tableau Education.

Thank you so much.

Chris

• ###### 3. Re: Don't want to Colour One Column on a Heat Map

Hi Chris,

You're welcome!

I'm not sure what you want, from your description it sounds like the Measure Names pill could be your Result Category. Could you swap Measure Names in relationship to the other pills? For example, in the workbook I'd originally attached on the Difference Calc view if you swap Measure Names and the Subject pills, you'll get all the Subjects for each measure, while the calculations stay the same.

If it's something different, could you provide a packaged workbook and/or some diagrams of what you'd like?

Jonathan

• ###### 4. Re: Don't want to Colour One Column on a Heat Map

Hi Jonathan,

Sorry for the confusion.

Please find attached a PDF of my current workbook and dashboard.

The first 3 sheets workout A*-A, A*-C and A*-G with the 4th being a dashboard of the 3.  I am hoping to have all 3 categories on one worksheet.

Hope this makes sense this time.

Chris

• ###### 5. Re: Don't want to Colour One Column on a Heat Map

Hi Jonathan,

This is my Current Position.

My aim is the have A*-A, A*-C and A*-G on the same Marksheet so that the 3 Marksheets on the Dashboard do not not jump out of Sync.

Thanks

Chris

• ###### 6. Re: Don't want to Colour One Column on a Heat Map

Hi Chris,

Is the attached what you're looking for? It uses the Measure Names/Measure Values technique I described earlier.

Jonathan

• ###### 7. Re: Don't want to Colour One Column on a Heat Map

Hi Jonathan thanks for your work on the above.

I had managed to do that, what I cannot seem to achieve is what I have on Dashboard.1 of the workbook where all the A*-A, A*-C AND A*-G are grouped together.

On another note (I think I know the answer to this and it isn't the ne I want) is it possible to Freeze the GCSE column so that the user cannot move it or deselect it and also is it possible to do a lookup on a specific column eg. GCSE as opposed to picking out the offset.

Chris

• ###### 8. Re: Don't want to Colour One Column on a Heat Map

Hi Chris,

How's this? I swapped Measure Names and the AH Session pills on the Columns shelf.

As for freezing the GCSE column, I don't know of any way to do that.

I believe the GCSE column in this view is coming from separate rows in the underlying data from the other values of AH Session, and in that case the way Tableau lets you use details from one row when showing details from other rows is via table calculations, which use offsets. If the GCSE and other AH Session values are all from the same rows, then you can use things like IF statements to use those values in calculations.

Jonathan

• ###### 9. Re: Don't want to Colour One Column on a Heat Map

Joanthan,

I thougt I had tried that bt can;t have.

That is fantastic - I will have a look at the IF Statements.

The leaning curve is huge and I am starting to get how Taleau Works thanks to you and your very helpful posts of which I have read many.

Once again thanks so much.

Chris

• ###### 10. Re: Don't want to Colour One Column on a Heat Map

Hi Richard,

Hopefully you can answer me another question on the Workbook above.

The Workbook above works out the Residual or  Grade Difference by having a Predicted Grade Column.  I shape the Data in SQL to Add this Column to the SQL Data.

A more efficient way would be to use your Difference from Calculated Field Technique and have 3 of these - one for each Category.  A*-A, A*-C and A*-G.

Am I correct in thinking that when you use the Measure Names and Values you cannot get  the option for Multiple Mark Types.

I have attached the Workbook where I have edited the A*-A, A*-C and A*-G Residual Calculations and the edited Residual Color (discrete) Calculated Field but all that happens is A*-C and A*-G Colouring just mimics A*-A.

Chris

• ###### 11. Re: Don't want to Colour One Column on a Heat Map

Hi All,

Just giving this a bump in hope that I can get answer as to whether this can be done with a workaround.

Thanks in anticipation.

Chris

• ###### 12. Re: Don't want to Colour One Column on a Heat Map

Hi Chris,

You are correct - Measure Names/Measure Values has only one Mark Type, which means only one Color shelf for the all values, and there's no access to what the Measure Names/Values are as individual variables.

An alternative is to do something like what I described here, where you use fake axes and then show the values as mark labels:

http://community.tableau.com/message/171852

Jonathan

• ###### 13. Re: Don't want to Colour One Column on a Heat Map

Hi Jonathan,

I have tried those techniques as I am very familiar with that page as I have learnt a lot from it.  The problem is on the Columns I would need to Partition by the Fake Measure Name which would be a Green Pill and then by AH Session which is a Blue Pill and you cannot put Green before Blue.

That is the reason I am a little stuck.

Chris

• ###### 14. Re: Don't want to Colour One Column on a Heat Map

Hi Chris,

The fake axes technique is a way of getting around two limitations:

- Tableau drawing too many or not enough headers (discrete/blue pills)/axes (continuous/green pills), like what you described

- Not being able to individually format Measure Names/Measure Values

The technique takes requires you to have the appropriate measure for each desired column in the data, so in your case I'm pretty sure you'd have to take your A*-A calculation and create versions of those for each value of the AH Session dimension that you want to show. Then you can have the custom color for each calculation.

Also, I'm not sure how the lookup calculation for getting the difference between the GCSE and other scores will work in this situation, you'd have to play around with it. This workaround is really a hack that can be more difficult than we'd want it to be.

Jonathan

1 2 Previous Next