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

# Double aggregation in Tableau

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

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

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

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

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