5 Replies Latest reply on Mar 2, 2012 2:41 PM by Alex Kerin

# How to see how many customers with one sale, how many with two, ...

I think that will be a simple question, but I can not find the solution now.

I use the SuperStore Sales sample and I can get a sheet with how many recods has every customer (see Sheet 1). The result are

Now I would like to have a report that tell me how many custumer has 1 sale (in the example below 6), how many has 2 sales (17 because the example below is only a partial one, see twbx attached), and go on. Something than:

Number of sales      Number of customers with that number of sales

1                              6

2                             17

3                            ...

Can any of you help me?

Thank you very much,

Teresa

• ###### 1. Re: How to see how many customers with one sale, how many with two, ...

It's not anywhere as simple as you would think - I'm really struggling to think what to do - it's absolutely doable with window calcs, but it's not coming to me so far.

• ###### 2. Re: How to see how many customers with one sale, how many with two, ...

I had to work through this one a few months ago for a histogram-like view, that was relatively straightforward. The table version required the special sauce of turning off "Ignore in Table Calculations" so the "bin" of # of orders per customer would be used to calculate the # of customers falling into that bin.

One thing to note is that I'm using COUNTD() instead of COUNT() to get unique order id's, and to do that for Excel, Access and text files in Tableau you have to use a Tableau Extract, since the MS-JET engine that Tableau uses doesn't support COUNTD().

Here's what I did:

- Created a CountD of Orders field that is just COUNTD([Order ID]) to get distinct order IDs.

- Put that field on the Rows Shelf.

- Click on the pill and set the pill to be discrete.

- Click on the pill and uncheck "Ignore in Table Calculations"

- Drag Customer to the Level of Detail shelf. There will be an overlapping marks error, ignore it.

- Create a calculated field - I called it Count of Customers (WC) - that looks like this:

IF (FIRST()==0) THEN

WINDOW_COUNT(COUNTD([Customer]), 0, IIF(FIRST()==0, LAST(), 0))

END

The IF and IIF functions are largely about removing overlapping marks, Richard Leeke wrote this up here:

http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html

The key bit is the WINDOW_COUNT(COUNTD([Customer]) function.

- Drag that field to the Text shelf.

- Click on the Pill and set the Compute using to Customer. The calculation will now compute along Customer for each CountD of Orders.

See the attached workbook for an example.

Jonathan

• ###### 3. Re: How to see how many customers with one sale, how many with two, ...

Jonathan,

Thank you for your very clear answer and the twbx example. I can understand it very well and I would be able to complete my work.