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

    dimension

    Winson Lui

      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
          Simon Runc

          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
            Alan Toomey

            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.

             

            pivot.PNG

            This would then allow you to create the chart you need easily.

            Workbook attached.

            • 3. Re: dimension
              Simon Runc

              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
                Winson Lui

                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
                  Alan Toomey

                  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
                    Winson Lui

                    Thanks, Alan.

                    Your advice is very useful.