4 Replies Latest reply on Jan 29, 2013 11:56 AM by Rudra Pratap

    Double aggregation in Tableau

    Rudra Pratap

      Hi,

          I just came through a requirement that I thought is easy but didn't worked.Let me explain you the scenario.

          I have following set of data.

       

         Customer  Order Quantity

         Rudra             2

          Sweta             1

         Ankit               5

          Manabh           3

          Rudra              2

          Manabh           4

          Manjeet           2

          Sweta             6

          Ankit               3

          Djoko              2

       

         I want to create buckets based on the sum(Order Quantity).The buckets will be like 0-3,3-5,5-8,others.The output that I want is like the below one.

         

         

          Bucket       Count(Customer)

           0-3                     2  (count of Customer where sum(order quantity) is between 1 & 3)(Manjeet,Djoko)

           3-5                     1  (count of Customer where sum(order quantity) is between 3 & 5)(Rudra)

           5-8                     3  (count of Customer where sum(order quantity) is between 5 & 8)(Sweta,Ankit,Manabh)

           others                 0

         

      I am not able to show the count of customer based on the sum of order quantity.

      Need help in this regard.Waiting for your response folks.

       

       

      Thanks & Regards,

      Rudra

        • 1. Re: Double aggregation in Tableau
          Joshua Milligan

          Rudra,

           

          This is a difficult one as the bucket or bin is based on an aggregation.  I was able to get what you were looking for using a couple of table calculations.  The "others" bucket doesn't show, but would if there were one or more customers that fell into it.

           

          I've attached a workbook so you can see how I came up with a solution.  Feel free to ask if you need any clarification.

           

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Double aggregation in Tableau
            Rudra Pratap

            Thanks Joshua,

             

                         This is exactly what I wanted.

                        But the workaround seemed complex to me.

                         I tried working out on showing the same in graphical view but it didn't happened.

                         As I want an action filter on this chart to show the details of the customers...

             

            Thanks & Regards,

            Rudra

            • 3. Re: Double aggregation in Tableau
              Joshua Milligan

              Rudra,

               

              I agree -- the table calculation solution to this is very complex.

               

              I'm wondering if there is a way to aggregate your data at the source.  Where is your data stored? If it is a relational database, then perhaps you can use Custom SQL so you don't have to worry about doing the aggregation calculation.  The Custom SQL Statement would look something like this for your data source:

               

              SELECT Customer, SUM([Order Quantity]) AS [Order Quantity]

              FROM Table

              GROUP BY Customer

               

              Then the calculation for the bucket would be a Row Level calculation and Tableau wouldn't have any trouble counting the customers in the bucket.

              • 4. Re: Double aggregation in Tableau
                Rudra Pratap

                Ya Joshua.

                Thanks for the workaround.

                 

                      I am using relational database itself.But the problem here is that in my dashboard I have 4 charts with some global filters etc.Only one of them is the customer wise distribution.Other charts are showing the facts based on different dimensions . I am using a single query for that.So,It will be difficult for me to change the query or add a new connection(because of global filters and all) .

                 

                   Thats why I am looking for a solution which keeps my whole sheets intact.

                 

                Thanks & Regards,

                Rudra