5 Replies Latest reply on Jun 22, 2018 7:54 AM by Don Wise

    Multiple Pivots and duplicate data

    Shai Rosenzvit

         Hi, I am new to Tableau, and so is the company I work for. The company has gaming terminals in different regions and lots of sales points.

         I have an excel file, which gets updated daily and a new sheet (for the new month) is added monthly, in which I have the following columns in each sheet:

       

      • Region
      • Sales point
      • Amount of terminals we have
      • Amount of terminals the competition has
      • Daily revenue (a column for each day)
      • Projected revenue for the month
      • Last month revenue
      • Current month profit
      • Competition revenue for the last 5 months (one month per column)
      • Competition profit for the last 5 months (one month per column)

       

      I am trying to analyze trends over time for our sales vs the competition sales in different regions, the competition profit over time in X region, our margin over time in a specific sales point, etc.

      I´ve tried doing a Union of the different sheets, then branching, pivoting competition revenue in one branch and competition profit on the other one and then doing a join, but I get inaccurate and duplicate data, the dates get messed up, etc. For example, if I try to see terminals in each sales point over time, I get 20 times more than we actually have, and consistent over time even if this has changed. ¿How should I clean and organize the data to be able to analyze it properly?

       

      I am attaching a screenshot of the flow I tried using in Tableau Prep and an annonymized workbook with no analysis so you can see how the data is structured. I don´t know if I should attach any more info (the original excel file for example), I am new to Tableau and data analysis, so I am a bit confused and overwhelmed by all of this.

       

      Thank you very much in advance for your help

       

      Cheers

       

      P.S. I am using Tableau Desktop Professional 2018.1.0 and Tableau Prep 2018.1.1

        • 1. Re: Multiple Pivots and duplicate data
          Jim Dehner

          Hi

          I took a look at your data - - there's a lot of it -

           

          Suggest for what you are trying to do - start with Tableau prep - use multiple steps to pivot and aggregate the data sets first individually to get them the way you want .so there is a consistency of data across the data files

          Then combine them - using either a blend or a join whichever is appropriate -

           

          Godd luck

          Jim

          • 2. Re: Multiple Pivots and duplicate data
            Don Wise

            Hi Shai,

             

            I was able to UNION, not JOIN, the worksheets without a problem.  Appears clean but only you'll know for sure whether they are or not.

             

            I did need to do a custom split on the Sheet Name (i.e., ABR 2018) to get to a Month/Year dimension (not a true date...would be nice if there was  date/year in one of the columns).

             

            Please see below and attached as example.  To avoid bringing in TOTALES those can be excluded to allow Tableau to do its own totals.  Lots of flexibility in Tableau.

             

            Difficult to understand what all your data means without some additional context...the lower the level of granularity, the better that Tableau does though.  So if you have daily sheets, you can Union them as shown below screenshot.  But should have a proper date stamp at very least in one of the columns to help with trending.  Also, I just realized that many of those entries indicate individual sales points by day of the week/month and then are already aggregated up into a subtotal? Those entries might be affecting outcomes.

            Screen Shot 2018-06-20 at 10.04.31 AM.png

            Screen Shot 2018-06-20 at 9.46.34 AM.png

            Screen Shot 2018-06-20 at 10.02.20 AM.png

            Screen Shot 2018-06-20 at 9.47.36 AM.png

             

            There are many, many, many training resources available to you to help you get started on the Tableau Training site...hope this helps.  If this does, please mark the response as either helpful or correct to close the thread.  Thx, Don

            1 of 1 people found this helpful
            • 3. Re: Multiple Pivots and duplicate data
              Shai Rosenzvit

              Thank you very much Don, I really appreciate it and your solution goes a long way to solve my problems. I will ask for a proper date to be added, just month and year will do as I don´t really look at daily results, I could even remove those to avoid clutter and duplicate results.

              What I´ve been wondering is how to solve the "Competition Sales 1,2,3" and "Competition Profit 1, 2 ,3" problem, because, those are actually their sales in May, April, March, etc. but they mean different things for the different sheets. I´ve tried doing a UNION of the sheets (only keeping the competition sales and profit for one sheet). pivoting Sales and Profit separately, and renaming the pivot names for each month and year, and then doing a JOIN before the output. But that´s when I end up with multiple results, incorrect data and dates.

              I am attaching the Tableau Prep flow I´ve created to try and solve this problem. I hope I am communicating the problem correctly, I think the problem lays with having the same data in different sheets, but even when I don´t import that data, I still end up with 90 terminals in 1 sales point.

               

              Thank you very much for your help

               

              Cheers

               

              Shai

              • 4. Re: Multiple Pivots and duplicate data
                Don Wise

                Hi Shai,

                Please see attached workbook based on the Tableau Prep Flow Output that was provided. The flow might need some fine-tuning but appears useable. I will look at the original data from the other workbook and see if I can get you a different Flow.  As we noted earlier the daily sales can be confusing and could be removed leaving simply the aggregated totals.  You'll need to check the machine totals on this workbook to see if the methodology is good or not. 

                 

                I simply looked at Region 1 to validate machine counts against the data in the data pane. 

                 

                The difference between the Flow Output and the joining of the sheets in the Data Pane of Tableau itself is that i had to change the calc from a COUNT to an AVG and then SUM the totals (not allowing automatic totals). So, both methods seem to be workable.  The manner of date (month/year) works well for the structure of this set of data.  See my other notes below screenshot:

                Screen Shot 2018-06-21 at 4.17.31 PM.png

                I'm getting the impression that the monthly reports are pre-aggregated as the totals are all in there.  So if the daily numbers are pulled out or excluded it would simplify things. I'd need more information as to what you mean by solving the Competition 1,2,3 Sales & Profit problem.

                 

                Thx, Don

                • 5. Re: Multiple Pivots and duplicate data
                  Don Wise

                  Hi Shai,

                   

                  As followup to the previous post, I've attached a revised Excel file and new Tableau Prep Flow file and the below are the steps taken to get the new output (.hyper) also attached. Hope it helps you out!  If it does, please mark the thread closed by marking it correct. Thx, Don

                   

                  First Union.png

                  Next Union.png

                  Final Union.png

                  Last Step prior to Output.png