6 Replies Latest reply on Jan 22, 2012 3:55 PM by Dimitri.B

    Order of columns

    Amy Song

      Hi guys,

      I have a question regarding display of columns.  If you refer to the dashboard in the attached, right now columns are shown: Passengers, market share, amt change from prev year, % difference, etc... then next to it the previous year's measures.

       

      I would like it to show 2011 passengers, market share, 2010 passengers, amt change, % diff, etc...  There are several more metrics in this data view, and some are from a different data source, and some are combined calculated from both sources.

       

      Is there a way to change the orders of the columns around how I have it set up?  Currently I am operating within Tableau's features using filters and table calculations for time range and metrics (so it shows all of 2011 first, and then measure for 2010).

       

      I have explored using calculated fields but hit a wall because fields calculated from the secondary source are joined based on month (so you would have to work in time-specific localized calculated fields).  There are 15 measures so it did not seem practical to approach it this way, at least in my limited working knowledge of Tableau.

       

      Furthermore, localizing time fields too much in calculations seems like would make it impossible to toggle between a month view, a quarterly view, and year to date month/quarterly views by filtering to months necessary.  From my explorations so far if time is localized in calculations, you would need to create exponentially more calculated fields to show them all separately.  Please correct me if I'm wrong.

       

      I would really appreciate if someone could show me how to set up my view so year over year measures are adjacent.  Keep in mind, this will be a monthly report going forward though, so calculated fields would have to be replicated annually.  What is the best method to approach in this situation?  Really appreciate your help!!

       

      Amy

        • 1. Re: Order of columns
          Dimitri.B

          I would have done the custom calculations. I didn't quite understand the localised times issue, but I did notice in the data that year and month are numbers, not dates - this would definitely be an issue drilling down through time.

          Is there a way you can either join the data before it gets to Tableau or somehow resolve the 'localised' time issue?

          I don't think there is a way to mix and match on dashboard fields from different sheets.

          • 2. Re: Order of columns
            Amy Song

            Okay, I am trying it with calculated fields, but I can't seem to get my tables to join.  Would this be related to the month/year not being in datetime format?  I have set my table relationships to airline_name, traffic_month, traffic_year.  I want to perform a simple division between a measure from the first table and the second (named "Calculation2") but nothing shows up when I draw it into the view?  Can you help me troubleshoot the join?

             

            Thanks,

            Amy

            • 3. Re: Order of columns
              Dimitri.B

              To make it work, I had to fix two issues in your workbook:

              1. One of the data sources has trailing spaces after each airline_name -  I created calculated fields in each data source that trim all leading and trailing spaces from airline names

              TRIM([airline_name])
              , and called it
              Airline


              2. Tableau works better with proper dates, so I used

              traffic_year
              and
              traffic_month
              to create a proper date via calculated field, and called it
              Date


               

              After that I edited joins by switching to Automatic, then back to Custom and deleting all except date dimensions and Airline, and it worked "as advertised", see attachment.

               

              Out of curiosity, some airlines had load factor over 100% - do they let people stand in the cabin?

              :)

              • 4. Re: Order of columns
                Amy Song

                That is perfect, thank you so much Dimitri!!  That makes sense... definitely will keep in mind in the future.  We are still working on auditing the seats data so some of those may not be 100% accurate.  Just wanted to make sure all the data connections and settings worked first.  Thanks again for your help!

                • 5. Re: Order of columns
                  Amy Song

                  Dimitri,

                  For the "rolled up to year" I am having trouble looking at different points of the year.  For November year to date, I uncheck December and it looks like the traffic number changes to reflect the month filter but the seats do not.  For example Air Canada has 80,940 total seats for the 2010 year and 64,115 passengers.  November year-to-date has 59,839 passengers but the seats number remains 80,940.  It looks like the tables are joined on all formats of date so how come seats does not change and passengers do when filtering on month?

                   

                  Thanks!

                  • 6. Re: Order of columns
                    Dimitri.B

                    Strange...

                    I fixed it (attached) using table calculations, but I don't know why it didn't work.

                    Looks like it needs the Month dimension on the sheet - it works if you add it to Row, Column or Level of Detail shelves, but having it only in the filter is not enough. So I had to improvise with table calculations.

                    It kind of makes sense, but why having a dimension on the filter doesn't do the trick?

                     

                    Generally I try to avoid using Tableau's data blending, as I only have a patchy knowledge of how it works, and I find it confusing and limiting. Data blending would be the last resort for me, for obvious reasons, but it can be handy. I usually do the joins in data, before Tableau.