2 Replies Latest reply on Jul 30, 2016 5:10 AM by Alex Trouteaud

    Excluding duplicate cases in aggregate measures

    Alex Trouteaud

      I am new to tableau (v9.3), and am having an issue in working with my data that I have yet to solve. I've been searching for solutions for hours now. Basically what I have is data read into tableau (that I cannot easily change in at the source) that looks like this:

       

      identifierfruit

      001

      pear
      001pear
      002apple`
      002apple
      003banana
      004banana

       

      For reasons that I won't go into here (and can't change in the original dataset), I have several rows of duplicate data. I *wish* I only had one row for each value of [identifier] -> 001, 002, 003, 004. I am trying to create a simple bar chart for [fruit] using -> SUM(Number of Records). I want to exclude the duplicated rows (the "extra" 001 and 002 records), so that my results look like this:

       

      pear  1

      apple  1

      banana 2

       

      I found this thread (Removing Duplicate Records) and it was helpful, but it doesn't seem to work if I select Analysis->Aggregate Measures. And, I think I have to use Aggregate Measures in order to have a bar chart with labels that make sense.

       

      Any help is appreciated...please remember I am a total newbie!

        • 1. Re: Excluding duplicate cases in aggregate measures
          Simon Runc

          hi Alex,

           

          So the link you've provided is one way, but that was before we have FIXED LoD calculations. Prior to this you would have to do this using Table Calculations. Table Calculations are Aggregates of Aggregates, so we could take the MIN([Number of Records]) for each Fruit, and then WINDOW_SUM that result. The issue here would be that you'd always need Fruit in your VizLoD (i.e. in the Viz) in order for the Table Calc (and the Min) to run correctly.

           

          Now we have FIXED LoDs this task becomes much easier. In essence FIXED works the same way (in this example) but we can define the LoD that the Aggregate is run over, in the formula, and so will work regardless of our VizLoD. You might find this Quora answer on the types of calculation in Tableau and how to think of them useful

          Answer - Quora

           

          So for you issue, I created

          [Number of Records - Remove Duplicates]

          {FIXED [Fruit]: MIN([Number of Records])}

           

          So this runs a MIN Number of Records against each Fruit (so returns 1 for each Fruit)..and this result is returned at Row Level (at the level of Fruit) which means we can aggregate it again. So I've brought this in as a SUM and I just get a 1 for each fruit.

           

           

          Hope this makes sense, but please post back if not

          • 2. Re: Excluding duplicate cases in aggregate measures
            Alex Trouteaud

            Thank you Simon - this was very helpful! I modified it slightly to produce what I needed, which was unique values across the [identifier] dimension:

             

            [Number of Records - Remove Duplicates]

            {FIXED [identifier]: MIN([Number of Records])}

             

            Worked perfectly...thank you!