Jonathan - This workbook has been very helpful for conditional formatting! Thanks so much for sharing. One issue I'm having is with showing subtotals. When I add the subtotals for the "Markets" in your example, it appears there is two layers of data overlapping in the "Sales" column. I'm getting a similar issue with overlapping numbers in my workbook. Any ideas? Thanks again!
I'm not sure why I missed your post, I apologize for not responding sooner.
When you turn on totals for the columns, you are going to get overlapping marks in the totals row(s) anytime you are using the dual-axis techniques. This is because the dual axis is drawing two sets of marks on the same axis/column, but there's only one total cell to put the the two totals in. The workaround in that case is to create a separate worksheet that generates the totals you want to use and then combine the original and totals worksheets into a dashboard.
V4 Is an amazing piece of work. Thank You
I have built the 2 Attached Screenshots which is exactly what I want.
The problem I am haveing is that I have 16 Columns plus 16 on the Dual Axis and the Render is quite slow. When I look at the Crosstab it has 32 Rows for Each Row of Data. Is there anyway of speeding the Worksheet Up? I have done a side by side test with Just Measure Names and Tableau returns the correct amount of rows in teh Crosstab Quickly (Nothing is Multipled). Off course I then cannot Color Code my %'s. Is this correct or am i doing something wrong.
The multiple axis technique does slow things down because you're doing a lot more calculating to get all the fancy formatting. I'm presuming by "When I look at the crosstab..." that you did a Duplicate as Crosstab? That's Tableau's best attempt at creating a crosstab from the current view, it's not necessarily a good indication of what Tableau is actually doing to generate the current view. A better one is looking at the # of Marks, Rows and Columns that Tableau reports in the bottom of the window. (Even better, you can look at the Tableau logs to see how much work Tableau is doing in the database).
That said, I notice that there are four Quick Filters or Parameters in the Forum Action.JPG pic, and only one in the 2nd pic. Both of those can make Tableau slower to refresh. If there's any way to move the Quick Filters (and maybe even parameters) into a dashboard and use Action Filters to refresh the crosstab view, you can see significant speed improvements.
Also, at least as of Tableau 7.0 there are some performance hits based on the # of calculations and # of worksheets in the view. A forum poster recently reported having told by Tableau tech support to reduce the number of worksheets in a workbook to roughly 30 or less, and I and some other users have seen performance of the entire workbook drop as more calculations (particularly table calcs) are added. So anything you can do to reduce either/both of those can improve performance as well.
Finally, voting for any/all of the Ideas on conditional formatting, access to measure names/values, etc. would be nice to help guide Tableau developers to making this easier in the future!
Thanks for the tip re: The Crosstab.
Rows and Columns are Correct - Marks are doubled probably Due to the Double Access.
I will try some of teh above and see what I find. I will be using Tableau Server so will do some speed tests.
One more thing you maybe able to help me with is a bit of a strange one - I am going to workout the residuals for each grade and color code it depending on whether it is plus minus or the same - this isnt a problem apart from when I turn the Column into a Gannt the single digit figures disappear - I go bak to a a bar and they reappear - any ideas on that little starnge one?
Thank you so much - I cant tell you how greatful I am and what a fabulous piece of work V4 is.
Jonathan - Re: overlapping subtotal and total #s
I did actually find a work around to get the subtotals and totals to appear on the table correctly. In the Format Font menu, I made the color for the Total and Grand Total Pane white, which effectively got rid of the totals and subtotals completely. Another way to do it is to drag the metric you are showing in the column into the "Mark text" pill for the "Empty Column" field in addition to putting it in the "One" field Mark text. I believe this makes the two overlap.
It took me a little playing around to find these work arounds and I did not write down the steps like I should have so these instructions may not be completely correct.
Thanks again for your instructions!
Bar marks align text to the end of the bar. Gantt Bar marks align text based on the setting in the Label shelf, putting the text between the start of the Gantt Bar mark (set by the axis) and the end of the Gantt Bar mark (set by the Length). If you have not set a Length, then the text could vanish.
You're welcome! And thanks for writing up what you found out!
I'd really like to see the workbook mentioned in this post but its not available any more at the link below - is it available anywhere else?
Last night I presented an updated version of the workbook at the Boston Tableau Users Group meeting, it's on Tableau Public at http://public.tableausoftware.com/views/conditionalformattingv4/Introduction. The instructions are more complete, and there are a couple of new tricks with regards to getting Excel-like conditional formatting where some text in a column is red and other text not. Based on this, I'm removing the color and highlight columns workbook that I'd originally had here, please use the Tableau Public version for your needs.
Jonathan - just implemented this approach in a workbook last evening and wanted to say THANK YOU! The problem of showing color and shape coded metrics has been vexing me for some time -- wanted to use Tableau to automate an Excel based dashboard approach but the awkwardness of trying to do the conditional formatting was frustrating. Your dual axis approach is fantastic, and this morning I have a better dashboard that is now color blind safe! Thanks again.
my requirement is exactly same, the example dashboard is showing for the measures which are having the dimension status, but what about the individual measure with threshould values instead of status dimension.
Any sample package would really a great help to me.
Just adding another question to already asked question.
I was referring to one of the packaged workbook (Color Coding For Subject View PWB) in the discussion. I liked the workaround. But just wanted to check about the export thing
So when we export the table as crosstab, we get multiple sheets in excel. Each with dimension name and respective measure.
Can anyone explain whats happening here?
Is it possible to color the individual row or column instead of entire sheet and also how to color the background of row or column not the text ? I have send you the image of my workbook. can i color the background of % column only and not the entire sheet ? Please provide the needful solution.
1 of 1 people found this helpful
My first suggestion is "Don't do this". What I'm seeing in the screenshot you posted is a wall of numbers that was most likely originally generated in MS Excel or by people who learned dataviz techniques from MS Excel (or a similar tool). Yes, it's showing a lot of numbers, but it's not necessarily supporting users in making fast insights or meaningful comparisons. Trying to duplicate a view from Excel in Tableau is like taking one's brand new Ferrari to the grocery store to get everything for the next holiday meal. The Ferrari (or Tableau) can do that, but the better tool for the job could be your old Honda or Toyota (i.e. the current tool). By trying to build this view in Tableau you're finding out what Tableau isn't good at rather than what is good at, so my suggestion is to a) find out what users are really trying to find out from this viz and b) build views in Tableau that answer those questions.
If you are really stuck with a demanding user or customer and have no way to change the deliverable, then there are a few options:
1) If the dashboard has a fixed height & width then you might be able to get away with floating a worksheet for that single column over the rest of the dashboard.
2) Use the Gantt or Bar chart techniques in http://public.tableau.com/views/conditionalformattingv4/Introduction
3) Create a scaffold data source per Joe Mako's outline, you can see that technique in this Think Data Thursday talk: http://community.tableausoftware.com/thread/126855, I also recommend watching Joe's guest spot on the Tableau Wannabe podcast at https://www.youtube.com/watch?v=ZftjHLfohmE.
What Jonathan said: Do NOT do this.
Else I foresee much pain in your future!