2 Replies Latest reply on Jan 13, 2017 6:23 AM by Raphael Hernandes

    Finding out largest differences between components in two tables

    Raphael Hernandes

      Hi, everyone,

       

      So, I'm dealing with two huge tables in an union. They refer to donations made during the last two elections in my country (2016 and 2012).

       

      In this table, each row corresponds to a specific donation for a candidate. In the columns, we have information such as an ID for the donor (similar to Social Security number), the donor's name (with lots of typos, so the ID is more reliable), the candidate who recieved it, the amount donated, etc.

       

      So, every time someone makes a donation to a candidate, a new row is added. If someone donates 10x, their name will be in 10 different rows.

       

      Now, I need to find out who were the people (top-20, perhaps) with the biggest difference between 2012 and 2016.

       

      Here's an example:

       

       

      Donation IDDonation valueDonorDonor IDRecipientTable name
      11381078SC000008E100Luke Skywalker70744420255ChewieTable 1
      1424281078B10000Z75Leia Organa01804994664GeorgeTable 1
      4281078SS0005458A900Han Solo68381845875ChewieTable 1
      421481UUA6431628N300Obi-Wan Kenobi57385768195YodaTable 1
      11616138SC3PO559P600Luke Skywalkrr70744420255YodaTable 1
      75894175BB885098Y40Han Solo68381845875ChewieTable 2
      62661078GG088108C50Moff Tarkin98769127348Darth VaderTable 2
      84811674TY994983X100Han Solo68381845875KyloTable 2
      61661678R2D29386Q500Luke Skywalker70744420255GeorgeTable 2
      75983759JB947341N40Leia Organa01804994664YodaTable 2

       

      Leia Organa

      As you can see, Luke donated twice in Table 1. I would need to have those values added, in order to view his full donation value.

       

      Some of them only donated in Table 1 or only in Table 2. These ones don't matter.

       

      The output I need is somewhat like this:

       

      Donor IDTable 1Table 2Difference
      70744420255700500200
      01804994664754035
      68381845875900140760

       

      I'm a reporter. I can do some programming, but I'm quite new to this. I have no clue here. Any ideas?

        • 1. Re: Finding out largest differences between components in two tables
          Simon Runc

          Hi Raphael,

           

          So aggregating data at different levels (ie. by dimensions) is one of Tableau's strengths.

           

          On your problem, the attached shows 2 ways to achieve this.

           

          In option 1...I've just used SUM([Donations]) and with [Table Name] on the columns & [Donor ID] in rows, Tableau performs the aggregation as expected. I've then used the "inbuilt" quick table calculation (using Difference) to show the difference.

           

           

          I've also created the following LoD calculation...

          [Exclude Single Donations]

          {FIXED [Donor ID]: COUNTD([Table Name])}>1

           

          This does a COUNTD on Table Name for each [Donor ID]...and then only sets this to true where this equals 2 (i.e. a donation in both years). I can use this on the filter shelf to exclude single year donation donors.

           

          Options 2.

           

          not to use the [Table Name] dimensions in the view, but create a separate calculation for each year.

          [2012 Donation Value]

          IIF([Table Name] = 'Table 1', [Donation Value], 0)

           

          [2016 Donation Value]

          IIF([Table Name] = 'Table 2', [Donation Value], 0)

           

          As these are aggregated, using SUM (and doesn't contain the "other years" values), we get the values we need.

           

          I the create the difference using

          [Donation Difference Calc]

          SUM([2016 Donation Value])-SUM([2012 Donation Value])

           

          I've then also created a RANK on this

          [Donation Difference Rank]

          RANK([Donation Difference Calc])

           

          and using a calculated field (which equates to true or false on the filter) and a parameter

          [Filter on Top N]

          [Donation Difference Rank]<=[Show Top N]

           

          I can use the parameter to only show the Top N

           

          Hope that helps and makes sense, but let me know if not.

          1 of 1 people found this helpful
          • 2. Re: Finding out largest differences between components in two tables
            Raphael Hernandes

            Perfect!

             

            Thank you very much, Simon.