3 Replies Latest reply on Apr 10, 2018 2:59 PM by Jim Dehner

    Preparing Excel Data for a Visualization of Revenue from Two Departments for Multiple Clients in Tableau Desktop 10.5

    Rebecca James

      Hi,

      I am very new to Tableau – I have a bit of Software experience – but feel a bit clueless with Tableau as I try to accomplish specific tasks. For my learning purposes, I realize that I am generally ok in terms of what to do with visualizations, and my challenges lie in preparing / cleaning the data in a way that it makes sense and makes it compatible with Tableau for said visualizations.

      Background: I work for a small legal firm that is transitioning from a paper and pencil kind of firm with respect to forecasting or analytical methods in place to a firm that is trying to implement software like Tableau to do some of that stuff for us. I transitioned from another role in the firm to this, and my current job is to learn Tableau so we can hopefully implement it down the road – so all and any help is much appreciated as I begin to learn.

      Here is my current project. We do revenue recovery for medical providers, and the current data source is the revenue generated for 12 clients, from the years 2007-2017. The revenue generated is from two different departments (two different legal areas), and department 1 is broken down into “Settled” and “Awarded” indicating the manner in which the revenue was generated for that department.

      Each of the 12 clients have their own table, and each table contains 6 columns.

      ·        Year

      ·        Department 1 Settled

      ·        Department 1 Awarded

      ·        Department 1 Total (Dept. Settled + Dept. Awarded)

      ·        Department 2 Total

      ·        Grand Total


      Not all tables have data for all years, depending on when they became and client and if/when they stopped being a client.

      This is a sample of how the data looks at present (There are 12 tables like this):

      Sample Table for Tableau.png

      The reason I am looking to use Tableau for this is:

      A. because I am still in the trial period, I’d like to see if this is the software we want to use and to show my team members what Tableau could do for us and
      B. obviously, Tableau could do far more with this than Excel ever could. Notably, I could hide and remove different departments depending on which revenue I’m looking to see, and look at different clients as well as using different graphs. (I am aiming to use a line graph to see the growth in revenue, with the possibility of using a column or bar graph.)

      That being said, I’d like to know how to upload the data in a way that makes the above possible. I don’t know if I should add other columns with tags (i.e. a column for different clients, tagged by their assigned acronym) or perhaps eliminate one or two of the columns (not having “Total” columns and just having the other 3 columns, as I’m sure Tableau can total them anyway for me anyway).

      I am not sure if it is helpful in this case, because my problem in this case has less to do with the actual visualization and more to do with preparing the data in a way that makes sense so I can get to the visualization. However, per forum rules / suggestions, I have published a sample workbook (I am on the trial period still, so I can’t export a packaged workbook in this case). Of course, for the aforementioned reasons of it being a data prep issue, all I was able to achieve on the workbook was putting the years in the column box. However, it is my understanding the workbook is downloadable, so I do it is helpful to see where my data issues may lie.

      The link for the sample workbook is below:

      https://public.tableau.com/views/2007-2017ClientRevenueforTableauUpload/Sheet1?:embed=y&:display_count=yes&publish=yes

      ANY and all input is very much appreciated. I apologize for being perhaps clueless, as I certainly don’t want someone to do the work for me, I just need some guidance on how to clean/organize and upload the data in a way that makes sense and helps me get to the visualization I want to achieve.

        • 1. Re: Preparing Excel Data for a Visualization of Revenue from Two Departments for Multiple Clients in Tableau Desktop 10.5
          Jim Dehner

          Hi

          Welcome to tableau and thank you for asking about data organization and structure

          Tableau is very different from Excel in the way it handles data - Tableau is database orient and works on Dimensions ( Client, Year, Department) and Measures (values)

           

          The ideal structure for Tableau would be a tall thin column (vz short and wide)  like this

          with this type of structure Tableau will separate out the clients , the departments , awarded / settled and do all the totaling - by year , YOY and if you have a principal or associated assigned to each client it can include that information also

          If you had a table like this structured for each client they can be brought into Tableau using what is called a WildCard union (link   Union Your Data and Tableau will put them together so the are all in a single file.

          Next year you would add in a single file with all 12 clients for the single year and again union it to the existing

           

          Hope this helps (at least a little)

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Preparing Excel Data for a Visualization of Revenue from Two Departments for Multiple Clients in Tableau Desktop 10.5
            Rebecca James

            Thanks for getting me started, Jim! Your advice is a huge help and a lot is already making more sense.

             

            Preparing Excel Data Tableau Community Question - Table 1.png

             

            A couple questions that refer to the image pasted above (so you can see exactly what I’m asking)

             

            1. ) I see in the image you pasted in your response, you included only Dept 1 and a separate column for Awarded and Settled. Do you have any advice as for where the data for Dept 2 should be placed? (In the above image, I have included it immediately after Dept 1, but left blank the column that distinguishes “Awarded/Settled” as the data for Dept 2 does not make those distinctions.) Should Dept 2 be placed after Dept 1 as it is in photo, or should it be placed elsewhere or on its own sheet? Also, should the column to the right include its own label for Department 2, such as “Total” or is it best left blank as it currently is?

            2. ) For the “Wild Card” Union, should the table for each client be placed on its own sheet in excel and then uploaded as a data source (12 sheets on one excel file)? Or should it be 12 different excel files? Or can it all be on one very long table, so long as each client is number appropriately (like in the photo below in which the table continues for about 400 rows, and each client’s data is copied one after another, with each client numbered 1-12)?

             

            Preparing Excel Data Tableau Community Question - Table 2.png

             

             

            Thanks again for your help!

            • 3. Re: Preparing Excel Data for a Visualization of Revenue from Two Departments for Multiple Clients in Tableau Desktop 10.5
              Jim Dehner

              The column with department 1 would be for any department.  So department 2

              would simply follow in the next row of data. They can be intermixed.

              Tableau will keep them straight.

              Q2 if you have your data by client then 12 separate sheets in a single file

              would be preferred.

              Jim

               

              On Tue, Apr 10, 2018, 4:43 PM Rebecca James <tableaucommunity@tableau.com>