6 Replies Latest reply on Dec 14, 2016 11:09 AM by Jian Wang

    Join two data sets - Appropriate join

    Ram Atmakuri

      Hello Friends,

       

      I am working on a dashboard where the data is coming from two tables (For ease I have copied only few lines of data in excel)

       

      1) Invoices - Invoice amount by month and project id

      2) Revenues - Revenue amounts by month and project ID.

       

      But when I am trying to join the tables I see that the values are duplicated, I believe by the number of times the revenue lines are present. Attached is the excel and the tableau dashboard and would like to seek your help fixed to how 

       

      Rows

      1) Project ID

      2) Invoice amounts

      3) Revenue Amounts

      4) Var - Invoice Amount - Revenue amounts

      Filter

      Month (To enable user to select the month and the data should also only for those months

       

      so as to show something like below.

       

      Capture.JPG

        • 1. Re: Join two data sets - Appropriate join
          Jian Wang

          Hi,

          First of all, the date field in both sheets are in different format. I have converted the Revenue Date format to MMM-YY.

           

          For the relationship, by joining Proj ID, you can get the report as you requested. However I don't see how you could incorporate Month ( I assume it is Revenue Month) into the report. What do you want to show when user select a renenue month ie. DEC-16?


                

          PROJ IDMONTHInvoice AmountPROJ IDMONTHRevenue Amount
          175117MAR-161,509,082.44175117Mar-1636,750,685.95
          175117NOV-143,772,706.10175117Dec-16311,247.00
          175117DEC-139,431,765.25175117Jun-160
          175117NOV-1318,863,530.50185012Mar-161,953,130.00
          175117SEP-133,772,706.10185012Dec-16-1,953,130.00
          174198DEC-13532,155.71190995Mar-163,115,399.57
          174198DEC-141,138,447.04190995Dec-1658,494,309.19
          174198OCT-13531,991.39260451Dec-162,000,000.00
          185012NOV-16-1,953,130.00
          185012MAR-161,953,130.00
          190995AUG-16-5,530,329.56
          190995DEC-1558,494,309.19
          190995SEP-165,530,329.54
          190995MAR-163,115,399.57
          • 2. Re: Join two data sets - Appropriate join
            Ram Atmakuri

            Thanks Jian Wang,

             

            to be more specific, invoicing and revenue booking are two independent events and are managed by two separate teams. In order to see how much invoicing is done vs revenue booking for a month by project is what we want to see.

             

            For example, if the user selects Dec'16 in filter, we will need to show

             

            1) the project ID,

            2) Invoicing activity for that month (from sheet 1)

            3) Revenue activity for that month (from sheet 2)

             

            Something to show like this (below is for all months assuming that the user selected all months). Hope this helps

             

            Capture.JPG

             

            Regards,

            Ram

            • 3. Re: Join two data sets - Appropriate join
              Jian Wang

              Can you show an example if user select Mar-16? What to be shown?

              • 4. Re: Join two data sets - Appropriate join
                Ram Atmakuri

                As there are 3 projects that had invoice transactions and 2 projects on revenue, it should show something like this

                Capture1.JPG

                • 5. Re: Join two data sets - Appropriate join
                  Ram Atmakuri

                  Hello Friends,

                   

                  Any help please

                  • 6. Re: Join two data sets - Appropriate join
                    Jian Wang

                    I am relatively new to Tableau. There got to be Tableau way of blending/joining data without manipulating data in EXCEL.

                     

                    For now, you can add a column called Revenue after column "Invoice"  in your Invoice sheet. Add a column called "Invoice" in front of column "Revenue" in your Revenue sheet. Then do a UNION.