Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Using summary data for bins and filters
I have been struggling to figure out how to create bins/filters based on summary data. Here are 2 simple examples:
1) Creating bins –
Sample data:
Custid orderid
100 A1
100 A2
200 A3
300 A4
400 A5
400 A6
400 A7
I would like to be able to create output that will bin the Total # of orders and count the distinct # of customer ids:
# of orders # of customers
1 2
2 1
3 1
2) Filtering: Using the same data above, let’s say I want to pull all the detail records for customers that have 2 or more orders(cust id = 100 and 400). From that point forward, I could perform all kinds of calculations. In SQL world, it would take 2 separate statements and look something like this:
a) Create table large_cust as Select custid, count(orderid) as numorders from table group by custid having numorders >= 2;
b) Select * from table, large_cust where table.custid=large_cust.custid;
Thanks in advance for your assistance.
Subscribe to the comments on "Using summary data for bins and filters"
Comments
I don't think you can do 1 in Tableau without using Custom SQL. The problem is that you want to aggregate twice. The SQL would look like:
SELECT COUNT(DISTINCT CUSTID), ORDER_CNT
FROM (
SELECT CUSTID, COUNT(ORDERID) AS ORDER_CNT
FROM TABLE
GROUP BY CUSTID
) AS T2
GROUP BY ORDER_CNT
Tableau can't yet generate that type of query.
To work around this, you can edit your data source, and choose the custom sql option. In the custom sql, put the inner query above. Tableau will then be able to generate the outer query. Not that you'll probably want a new datasource for this, as it will only be useful for this type of query. The original datasource will still be needed for your second question.
There are a lot of powerful things enabled by multi-level aggregation like this and we plan to integrate it into Tableau in the future.
For 2)
Rt click on CustomerID and select 'Create Set...'. In the Create Set dialog, Select all values, and then click the condition tab. In the condition tab, choose By Field and set it up so that it's COUNT(orderid) > 2. Name and save the set. Then by simply dragging the set out, you will restrict yourself to customers who have more than 2 total orders.
:)ross