I'm not exactly sure what you are trying to get to, but I think the problem is the 'shape' of the data. You are correct in your reason, that any if statement exits once it has found a satisfying condition. A formula like the one you have is performed at row level, and what it looks like you are asking for is that the same row get's reclassified differently, but in a single dimension?!!
I don't have the figures in your chart, so have used COUNTD on ID. In the attached (including the Excel) I have used the Tableau Excel Data Reshaper (free addd-in; http://kb.tableau.com/articles/knowledgebase/addin-reshaping-data-excel) to get your data into a 'Tabular' form. This means that any dimension (slices of the data) go down the page...such as Year. I've had to add an extra formula to split out the year from the 'Status 2011' description.
Once we have it in the correct Shape, we don't even need a formula. We can show each value by year, and colour on status.
This is a bit of guess (I can see you have 5 bars and 5 years, so assumed you want a bar each year, coloured by status), so if I've missed the mark please post back!!
Hope this all makes sense.
This may or may not work depending on the overall size of your data and if its more complex than your sample.
You can pivot the datasource (either on the datasource or in Tableau if its an excel source) so the Status fields are rows instead of columns.
This would then allow you to create the chart you need easily.
pivot data.twbx 14.8 KB
Thanks Alan...I'd completely forgotten about the new Pivot function!!
Winson...You can ignore the Excel Add-in, as you can now do this 'reshaping' directly in Tableau, as Alan has shown.
Thanks for the new technique.
If my excel dataset has 10 columns which I only need to make use of the first 6 columns (ID + Status2011 -2015) to do the charts.
Is this technique still applicable? i.e. Pivot the first 6 columns and keep the remaining unchanged.
Or I have to create a separate Excel spreadsheet for the first 6 columns only?
ID Status 2011 Status 2012 Status 2013 Status 2014 Status 2015 W X Y Z 1 A B A A B 1 1 1 1 2 A A B B B 1 1 1 1 3 A B A A B 1 1 1 1 4 B B B B A 1 1 1 1 5 B A B B B 1 1 1 1 6 B A A A B 1 1 1 1
1 of 1 people found this helpful
That should work out fine for you.
The extra colunms will duplicate for each row (Status) so you just need to take that new level of aggregation into account when your doing counts or calculations.
Also, if this is something you would be automating into the future you may need to think about how you handle this when 2016 comes along.
Your advice is very useful.