6 Replies Latest reply on Oct 9, 2015 4:00 AM by Winson Lui

# dimension

HI,

I created 5 measures and put them together to act like a series of bar charts.

Each of them is consisted of 2 segements, A and B.

At the same time, I would like to create a new dimension and put it on Colour to visulise the segments and make the bar charts to be stacked bar charts.

Here is the code I wrote and I found that the logic does not seem right.

The reason I suspected is in the excel dataset, the data set is like this and the first if statement in the code will dominate the following 4.

IDStatus 2011Status 2012Status 2013Status 2014Status 2015
1ABAAB
2AABBB
3ABAAB
4BBBBA
5BABBB
6BAAAB

Code of the dimension:

IF [Status 2011] = "A" then "A" ELSEIF [Status 2011] = "B" then "B"

ELSEIF [Status 2012] = "A" then "A" ELSEIF [Status 2012] = "B" then "B"

ELSEIF [Status 2013] = "A" then "A" ELSEIF [Status 2013] = "B" then "B"

ELSEIF [Status 2014] = "A" then "A" ELSEIF [Status 2014] = "B" then "B"

ELSEIF [Status 2015] = "A" then "A" ELSEIF [Status 2015] = "B" then "B"

END

Would you please let me know how I can modify the code or the dataset?

Thanks.

• ###### 1. Re: dimension

hi Winson,

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.

• ###### 2. Re: dimension

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.

Workbook attached.

• ###### 3. Re: dimension

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.

• ###### 4. Re: dimension

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?

Thanks.

IDStatus 2011Status 2012Status 2013Status 2014Status 2015WXYZ
1ABAAB1111
2AABBB1111
3ABAAB1111
4BBBBA1111
5BABBB1111
6BAAAB1111
• ###### 5. Re: dimension

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.

1 of 1 people found this helpful
• ###### 6. Re: dimension

Thanks, Alan.