4 Replies Latest reply on Mar 24, 2017 1:07 PM by Jay Morehart

    Subtract one Data Source from Another

    Christopher Smith

      Hello,

       

      I am new to Tableau and am using Tableau Public.  I have tried to find a solution through searching the forums and online tutorials, but have had no luck.  I'm sorry if this is just something I've missed!

       

      I am currently working with two data sets.  SetA includes total sales by date.  SetB includes total sales in some specific channels by date.  I would like to use this data to come up with a currently non-existing data set, SetC, that is the difference between SetA and SetB to give the remaining sales in channels not covered in SetB.  Is this possible?

       

      Thanks in advance for your assistance!

       

      Data looks something like:

       

      SetA

       

      DateUnits
      RevenueChannel
      01/01/175$500Total
      01/02/177$850Total

       

      SetB

       

      DateUnits
      RevenueChannel
      01/01/172$100Web
      01/02/171$250Web
      01/01/171$50Mail
      01/02/172$120Mail

       

      SetC

       

      DateUnits
      RevenueChannel
      01/01/172$350Direct
      01/02/174$480Direct
        • 1. Re: Subtract one Data Source from Another
          HUSSAIN SHAIK

          Hi Christopher,

           

          You can probably use Blending to subtract data in Tableau.attached workbook with demonstration

          1 of 1 people found this helpful
          • 2. Re: Subtract one Data Source from Another
            Christopher Smith

            Hi Hussain,

             

            Thanks for sending this.  Unfortunately, I cannot open it since it is not in Tableau Public.  Is there any way you can either upload it to Tableau Public or briefly describe your technique?

             

            Thanks again!
            Chris

            • 3. Re: Subtract one Data Source from Another
              Brian Connelly

              Christopher,

              You need to blend the data by joining on the date between the datasets.  Then create calculated fields to subtract Sum of Units in Set B from Sum of Units in Set A, i.e. SUM( [DatasetA].[Units]) - SUM([Units]).  Do this also for Revenue.  Both these new fields become your measures.  You can also create a field and hard code the channel as "channels not covered". 

               

              This was all done in what Hussain provided in his workbook sample.  

              • 4. Re: Subtract one Data Source from Another
                Jay Morehart

                Depending on what your end goal as far as analysis is, this method might be helpful, albeit somewhat round about:

                Assuming that you cannot edit the data sources, you can still the date in to the following format:

                which is essentially an aggregated view of a transactional data set.

                This allows the creation of a common cross tab with a totals column:

                 

                How I got here:

                Union Data set A and Set B

                You need the following 6 calculations:

                1: Web Revenue = { FIXED [Date] : Sum(if [Channel] = "Web" then [Revenue] else null end)}

                2: Total Revenue = { FIXED [Date] : Sum(if [Channel] = "Total" then [Revenue] else null end)}

                3: Mail Revenue = { FIXED [Date] : Sum(if [Channel] = "Mail" then [Revenue] else null end)}

                4: Direct Revenue = [Total] - [Mail] - [Web]

                5: [Channel (new)] =

                    Case [Channel]

                    when "Total" then "Direct" else [Channel] END

                6: [Revenue (new)] =

                    Case [Channel 2]

                    when "Direct" then [Direct]

                    when "Mail" then [Mail]

                    when "Web" then [Web]

                    END

                 

                Then using Channel (new) and Revenue (new) we have the data in the format as above.

                Sample workbook is also attached.

                 

                Hope this helps, let me know if this works or if you have any questions/issues

                 

                Depending on the data source, you could probably do something similar with custom SQL