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)


      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



      • 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.


        • 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.





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

          Combined 3.jpg


          Required Output.PNG.png