1 Reply Latest reply on Oct 4, 2013 9:12 AM by . Indumon

    Joining two aggregated data sets - combined view issue (missing values in one set)

    siim.v

      This may be a newbie question, however I have tried a number of different things and searched the forums and KB but am still stuck trying to make this work. If you feel there is a guide or a document that would help me resolve this then can you point me to it.

       

      I have 2 data sets.

       

      A) Sales data

      • Sales Date
      • Store
      • Product
      • Number of Transactions
      • Amount USD

       

      B) Returns data

      • Order Date
      • Store
      • Product
      • Order Type (is one of Refund or Cancel)
      • Number of Transactions
      • Amount USD

       

      Both are aggregated large data sets, coming from distinct SQL queries (unfortunately I am currently unable to JOIN this at SQL level hence trying to get this to work in Tableau - using v8).

      I want to view them at aggregated Month level (not all days have returns or have returns for all stores or product. Similarly there could be a return on a day where there is no sales in a particular store or product)

       

      Example here provided uses Excel test data. Attached both Excel and Tableau Packaged.

       

      What I am trying to achieve is a single view that would

      • Per month
      • and per Store
      • and per Product

       

      Show

      • Number of Sale Transactions
      • Number of Refunds (if no refunds for that month, store, product combination then Zero)
      • Share of refunds % (Refunds/Sale)

       

      I understand that the way Tableau internally does Joins or Blends data is the primary reason why I am not getting my desired result but the black box nature of some of the calculation logic applied there is eluding me here.

       

      Please help.

      Cheers.

        • 1. Re: Joining two aggregated data sets - combined view issue (missing values in one set)
          . Indumon

          Hi Siim, Your blended data source is enough to create the required output, but the data relationship "sales date= Order date" will not return the Product B figures since they are not matching.

           

          Work around:


          Step1 : Create two calculated fields in Sales data. Year=Str(Year([Sale date])) and Str(Month([Sale date]))

                    and do the same in Returns data. Year=Str(Year([Order date])) and Month=Str(Month([Order date]))

           

          Step2: Edit your data relationship as below.

           

          Relationships.PNG.png

           

           

          ---------------------------------------------------------------------------------Combined Sheet now

          Combined 3.jpg

          Required.

          Required Output.PNG.png