3 Replies Latest reply on Sep 25, 2018 2:34 AM by Donna Coles

    How to do group by in tableau

    shikha agrawal

      Hi All,

      I have below query in tableau.

       

          

      EmployeeIdAmountDate
      abc159.351/4/2018
      abc159.351/4/2018
      abc166.4212/14/2016
      abc166.4212/14/2016
      abc177.4912/18/2017
      abc177.4912/18/2017
      abc130.412/13/2017
      abc130.412/13/2017

       

       

      I need an output that i will get sum of duplicate items on amount like: 59.35+66.42+77.49+30.4 as they are coming twice.

       

      Kindly help.

        • 1. Re: How to do group by in tableau
          Donna Coles

          Hi Shikha

           

          If you don't want duplicates, then the ideal is to get these removed via some preprocessing outside of Tableau - will simplify everything else you may need to do.

           

          If this isn't possible, then an initial suggestion is to create an LoD field that is the average of the rows per date/employee/id/amount (ie the fields that I'm assuming define a duplicate).  This LOD can then be added to a Viz which can then be aggregated further (ie summed).

           

           

           

          Hope this helps.

          Donna

          1 of 1 people found this helpful
          • 2. Re: How to do group by in tableau
            shikha agrawal

            Hi Donna,

             

            Above answer is working in even number of values. Is it also working fine if I have odd number of values like below:

             

            EmployeeIdAmountDate
            abc159.351/4/2018
            abc159.351/4/2018

            abc                                        1                                                       59.35               1/4/2018

             

             

            In this case i want 59.35+59.35 because both are duplicates.

             

            Regards,

            Shikha Agrawal

            • 3. Re: How to do group by in tableau
              Donna Coles

              So you're trying to say that of the 3 rows you've listed above, that all look identical, only one is truly a duplicate of the other...? You might know this because you are incredibly familiar with the data, but how can the 'system' know this without some other unique row identifier...?  If you have 4 identical looking rows what would you expect the answer to be

              - 59.35 because its 1 row duplicated 4 times.

              - 118.7 because its 2 identical rows, that have each been duplicated once

              - 178.05 because its 3 identical rows, where one has been duplicated

              - 237.4 because all 4 are identical not duplicated rows...

               

              If you know the 'rules' by which your data becomes duplicated, then we can probably come up with some formula, but as I mentioned to start, the ideal would be to handle these duplications outside of Tableau.  If this isn't possible, then can you add any other identifier to each row in the data that means duplicates are more obvious?

               

              Donna