3 Replies Latest reply on Apr 5, 2012 1:52 PM by Alex Kerin

    Export Crosstab combines last column with messure

    Jaime Yunker

      My worksheet has multiple discrete "Rows" that have formulas in them. My measure is a Rank, calculated on the Running Total of the "# of Records."

       

      Everything looks great in Tableau, but when it is exported to Excel in a crosstab, the measure is combined with one of the "Rows."

       

      I've attached the full-size pictures for you see the details...

       

      Top Customers - Tableau.png

       

      Top Customers - Excel.png

       

      As you can see the "Ranking" and the "Q1 vs. 2011 Q4" get combined as one in Excel. Does anyone have any suggestions as to how I can get them not to combine?

       

      I've tried adding a "dummy" field with the hopes that the "Ranking"  would combine with it instead of the "Q1 vs. 2011 Q4" so I can see the  column.

       

      Thank you in advance for any suggestions!

        • 1. Re: Export Crosstab combines last column with messure
          Tracy Rodgers

          Hi Jaime,

           

          What Tableau refers to as a crosstab and what Excel defines as a crosstab are different. The following links provides information on what the definition of a crosstab is:

           

          http://en.wikipedia.org/wiki/Cross_tabulation

           

          http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

           

          A crosstab is expected to have measures that can be summarized across the table and since the measures that are in your view are discrete, the columns shelf is split into as many columns as there are unique entries for that discrete measure (it is being recognized as a measure by both Tableau and Excel). There are a couple of ways around this, the first is to simple copy and paste the view into Excel. Another option is to create calculated fields for all of the discrete measures on the view so that they are no longer recognized as measures.

           

          For example:

           

          str(sum([2011 Q1]))

           

          Then, replace those fields with these calculations. Finally, another calculated field will need to be created and placed to the farthest right of the view so that only one member is available and the crosstab won't have multiple columns. For example, create a calculated field that returns an empty string: (" ").

           

          In your case, this all seems a bit complicated and it may be best to use the copy and paste option, but at least hopefully this makes some sense!

           

          -Tracy

          • 2. Re: Export Crosstab combines last column with messure
            Jaime Yunker

            Thank you for the suggestion! However, it didn't matter if I used the "copy & paste" or the "export" option, I still go the same result.

             

            I also tried the STR( ) option and that worked until I got to the last 2 fields that utilize a table calculation. One has a "% of Total" and the other has both "Running Total" and "% of Total" and when I switched the field to a string, it wouldn't allow for the calculation.

             

            SO, what ended up working was... I took the discrete field that was getting combined, and "duplicated" it. I also made sure to leave the name marked as "copy" so that it would now be the last field in alphabetical order. Then added it to the layout, set it to discrete as well and removed it from the header and tooltip. Once I did that,  the original field was carried over to Excel as I had hoped, and the duplicate field was the field that was combined. Now, at least I can easily delete the extra columns.

             

            I originally tried doing a field named "Z" and just left it blank or ever equal to 1, but it wouldn't combine with it. It would only work if I duplicated the last field.

             

            It's definitely a work around and not a solution, but it will work just fine for what we need it for.

            • 3. Re: Export Crosstab combines last column with messure
              Alex Kerin

              I've sometimes used select all, view data, and then copied the summary out successfully.