I understand getting Tableau to do what you want can be frustrating, because Tableau does not let you do everything Excel allows you to do (they are very different applications, and I believe asking Tableau to be more like Excel is not what you should be asking for).
I can understand that sometimes you may ask "Why is this more difficult than what I think it should be?"
For myself, my answer is awareness there are constraints that are not visible on the surface.
tablesample.twbx 16.9 KB
Ah, Confucius Joe, a great many of us come from the Excel world so it's only natural for us to want to mimic its cross-tab functionality of formatting & conditional high-lighting.
What's particularly frustrating for me is that if it can't be done in Excel in a few clicks of the mouse, I can make Excel do it using VBA<evil laugh >. Not so with Tableau<wimper >.
Cannot be applied to muliple columns.
Thank you. Your comments are always insightful. I understand that tableau is a tool for data discovery but at the same time, tableau as a product has been pushing itself to become a leader in dashboard delivery. I think many would agree that tabular format, although not visually stunning, is still used heavily in workforce for data delivery (especially for summary).
Don't get me wrong, working with tableau pushes my creativity in the way to delivery contents.
You have answer part of my initial query. With your answer, I think I got the general idea on how to format One KPI. I still am puzzled on how to conditionally format multiple columns (KPIs).
But very cool on all the ranking tricks here.
Here is my scenario:
I have multiple KPIs to sort.
What I did was:
1) Perform all ranking on required columns
2) Unpivot all KPI
3) Use CASE statement to mark all cells needed to be highlighted. (will be used in Color shelf)
It seems to work although, what I lack in this approach is the filtering capability (the data needed to be ranked from source).
Your apprach still retains the filtering capability but I am sure it cannot be applied to multiple columns.
Toby, I too came from Excel, and used VBA frequently. I thought things were great, I felt like I could do whatever I wanted with the combination of Excel and VBA. Then I found Tableau, and I found all sorts of constraints, and then I found ETL software. So now With the combination of ETL software and Tableau, I feel like I can create any viz and perform any analysis, and have it be dynamic based on the underlying data, and interactive. About the only thing I use Excel for now is as a CSV editor.
Nopparit, I am unable to understand your description of what you have and what you are looking for. Can you please provide a packaged workbook that represents your situation, and mock something up to show what you want for a result?
Unfortunately, I am unable to align the image with your above text, can you please try explain it again. I do not see how the image represents any ranking, sorting, case select logic or KPI concepts.
To create the image you attached is very simple from the workbook I attached earlier, as done in the attached. I expect what you are looking for is doable, but I am just not able to understand your request.
tablesample_edit.twbx 27.5 KB
Joe, Thanks for your patience. Let's reset and try again with different example.
A sports meet with three individual events: Triple Jump, Pole Valut and Short Put.
There are three athletes participating: Athlete A, Althete B and Athlete C.
What I want to achive is to identify who comes first in each category (event) and color the background to show that that athlete comes first in that category. I also want to color each winner's color differently to identify different event.
The only ranking I am interested in DenseRank = 1 (tie allowed) for each category and the rest I am not interested.
I have attached the workbook and illustration of a mock up below
I hope the example is not too frivolous. I just think that it conveys my scenario perfectly and easily.
Hmm, clever calculation usage for Color...still trying to wrap my head around that. I think that's where people get hung up (myself included), is trying to understand how Tableau will 'react' to end results - particularly aggregated results.
I don't want to hijack this thread so you can email me for further discussion but what do you use for an ETL? That's something I haven't thought of.
Email: (first part is phonetic and delete all underscores) Tango Echo Romeo Kilo Sierra Oscar November @_go2uti_._com
And for everybody else, like they say on CraigsList.com, it's not alright to send email to that address for services or other commercial interests.
I highly recommend:
You will want to reshape your data source from:
Class TripleJump PoleVault ShortPut Athlete A 10 11 20 Athlete B 6.9 13 35 Athlete C 7.5 15 30
Class Event Score Athlete A TripleJump 10 Athlete A PoleVault 11 Athlete A ShortPut 20 Athlete B TripleJump 6.9 Athlete B PoleVault 13 Athlete B ShortPut 35 Athlete C TripleJump 7.5 Athlete C PoleVault 15 Athlete C ShortPut 30
Attached is one method that used this transformed data.
tablesample_2_edit.twbx 21.3 KB
"I don't want to hijack this thread so you can email me for further discussion but what do you use for an ETL? That's something I haven't thought of."
I too would be interested. Currently I'm using Perl to unpivot data, which works and is quick, but I'm always looking for new tools. Worth starting a new thread if Joe has the time and inclination to discuss?
Since there is much I still want to learn about data transformation and integration, and I consider myself a beginner, I would recommend trying a bunch out.
Currently I am learning Pentaho, and am very happy with it.
Additionally, there are many other applications available that are well worth an evaluation.
Hmm, Pentaho looks interesting but costly!