9 Replies Latest reply on Dec 6, 2013 7:16 AM by Jonathan Drummey

    Excel Report - Replicate in tableau

    buildstar

      Hi All,

       

      Please find the attached excel data.

       

      I need to constuct the same report in tableau. The report has in all columns from A - CZ.

       

      I want to display all the columns in the report. Please also note that all the columns will have data once the report has been developed.

       

      Also each row of data will have multiple records. So creating separate work sheets and lining them in a dashboard may not be best suited for this situation. I would rather want to create a single worksheet with all columns. Something similar to this post would be suited for this situation :

       

      Sometimes You Have to Break the Rules. Expanding Beyond 16 Columns without Header Concatenation | Tableau Software

       

       

      Please also provide a sample workbook with this data as source.

       

      Thanks

        • 1. Re: Excel Report - Replicate in tableau
          Shawn Wallwork

          Your sample data isn't enough to determine what each column is (or is going to be). If these are all dimensions then you will need to use the technique you linked to. I have some experience with this and can tell you that it is a performance killer against any real dataset, and I strongly recommend against using it.

           

          If you can organize your text table so all the dimensions are on the left and all your measures are on the right, then you can put up to 16 dimensions in your viz, and as many measures as you want by using Measure Names/Measure Values. I suspect you already know how to do this, but if you post a data set with one row of real data organized like this, I can put a workbook together for you.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Re: Excel Report - Replicate in tableau
            buildstar

            Hi Shawn,

             

            As requested please find the file updated with one row of sample data. I would also like to add that the dimensions are very less in number of columns.It would only makeup 10% of the actual data.

             

            Please help with the report display.

             

            Thanks

            • 3. Re: Excel Report - Replicate in tableau
              Eric McDonald

              If you ant to create a report with that many columns, then Tableau probably isn't the answer. Ask what the business requirements are first as there is probably a better solution.

              • 4. Re: Re: Excel Report - Replicate in tableau
                Shawn Wallwork

                What Eric said. But I've beaten my head against this C-Level mentality with little success (they do love their text tables). I'll take a look at new workbook in a bit.

                 

                --Shawn

                • 5. Re: Re: Excel Report - Replicate in tableau
                  buildstar

                  Sure Eric/Shawn....you are right.

                   

                  I also understand that tableau is not the right tool to deal with such text tables....but was curious if this can be done...if it can be done then I would have a solution to some of my problems in tableau.

                   

                  Thanks

                  • 6. Re: Re: Re: Excel Report - Replicate in tableau
                    Shawn Wallwork

                    You simply have too many dimensions to do anything with. You'll need to break this down into several text tables. In the attached workbook I simply copied two rows of Excel data and pasted it into Tableau, and T automatically created the attached workbook. (I increased the columns to 16 to get rid of some of the truncations.)

                     

                    Gallop, you really don't want to go down this road. If you tell them it can't be done now, then they can get that into their heads, and your life will be easier later.

                     

                    --Shawn

                    1 of 1 people found this helpful
                    • 7. Re: Re: Re: Re: Excel Report - Replicate in tableau
                      buildstar

                      Shawn,

                       

                      Unable to open the file. I am using tableau V8 . Please find the attached image.

                       

                      Haha....I completely agree to your advice. I will keep my users informed that this task is better not being done in tableau.

                       

                      Thanks

                      • 8. Re: Re: Re: Re: Excel Report - Replicate in tableau
                        Shawn Wallwork

                        Gallop, you can easily create the workbook yourself. Here is a little known/used technique for quickly creating a Tableau workbook and a viz. Highlight the first two rows of your spreadsheet. Copy using Ctrl-C. Now open a new Tableau workbook. Then just do a Ctrl-V paste and waatch the magic happen!

                         

                        Go to Analysis\Table Layout\Advanced and set everything to the max of 16.

                         

                        You'll have a viz with a whole lot of combined dimensions in the first pill on the left and 15 dimensions after it. So like I said this really can't be done in Tableau.

                         

                        Cheers,

                         

                        --Shawn

                        1 of 1 people found this helpful
                        • 9. Re: Excel Report - Replicate in tableau
                          Jonathan Drummey

                          First, I agree with Shawn & Eric. Joe Mako did a nice post on this subject at Re: Text Tables Again -- Arrrgh!

                           

                          This is not Tableau's strong point, for example in your original file the headers have different colors, there's no easy way to do this in Tableau except by using two different worksheets on a dashboard, or by coloring all the text or backgrounds of the data, or potentially by creating a fake "header" within your data and using that instead.


                          Secondly is a way to work towards this kind of layout in Tableau (with limitations), by unpivoting your data. The simplest technique is to use the Tableau data reshaper, Joe has been building some more advanced techniques where he populates a scaffold data source and then blends in the data, I'm not sure whether that would work in your case or not.

                           

                          1. Downloaded the data.
                          2. Added a Data Row ID column as column A.
                          3. Clicked on cell B2 and started the Tableau data reshaper. One thing to note is that the Tableau data reshaper skips all cells that are empty. and doesn't import them at all, so all those empty columns are ignored. If you want those in your data, you'll either need to populate them with some unique value that you can change later in Tableau or use a different reshaping technique.
                          4. Once the reshaper was done, changed the column names to "Column" and "Value". The data looks like this:
                            2013-12-06 09_43_16-Microsoft Excel - giant table.xlsx.png
                          5. Started a Tableau workbook with that data.
                          6. Put Data Row on Rows, Column on Columns, and Value on the Text Shelf:
                            2013-12-06 09_45_23-Tableau - giant table.png
                          7. Now that view doesn't have any special formatting, we can get that by creating new calculated fields for each different format. I set up one to turn column BK into % and another to get column G back to a time, you can see that here:
                            2013-12-06 09_45_55-Tableau - giant table.png

                           

                          There are still complications with this kind of view, here are some:

                          • If you're trying to do additional computations on this data within Tableau, it can get extremely complicated because the data is so unpivoted for display.
                          • The way Tableau does formatting and given there's a single Marks Card, there's very little you can do about left/center/right alignment for each column, there's one setting for the table. You might be able use a multiple axis crosstab within this to fake right and left alignment, and performance might nosedive if you did so.
                          • There's only one Marks Card, so you can't mix and match mark types for different KPIs.
                          • Exports of the data are going to look strange because of the unpivoted data and extra calculated fields.
                          • Printing and export to PDF may look strange, in my limited experience it works like Excel, with lots of pages.

                           

                          I've attached the .twbx and the Excel file that was used in the .twbx.

                           

                          Jonathan

                          1 of 1 people found this helpful