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.
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))
The IF and IIF functions are largely about removing overlapping marks, Richard Leeke wrote this up here:
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.
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.
Also your reference about Richard Leeke's post is very interesting.
Nice job Jonathan - I had got caught with two table calcs and of course I couldn't use one of them as the reference in the second.