3 Replies Latest reply on Apr 7, 2015 2:13 AM by Yuriy Fal

Rolling Count Distinct

Hi everyone,

I'm trying to get a rolling 12 month average of sales per customer.

Here's roughly the formula:

January 2015 Average Sales Per Customer =

(SUM(Sales between February 2014 through end of January 2015)/ (COUNTD(Customer IDs between February 2014 through January 2015))

I am able to get the numerator.  I put Year and month into my viz, then I used WINDOW_SUM(SUM([Sales]),-11,0).  That worked perfectly.

Then I tried to use window again for the demonator and things started to fall apart.

First attempt (Tab 1): WINDOW_COUNT(COUNTD([Customer ID]), -11,0)

Issue:  It was just a running count of columns in the pivot.  Not useful at all.

Second attempt(Tab 2):  WINDOW_SUM(COUNTD([Customer ID]),-11,0)

Issue:  This one was closer.  It did a COUNT Distinct on Customer ID per each much, and then summed that up.  It looked way better than attempt #1, but a COUNTD per month is not the same as a COUNTD for a year.

A more realistic result can be found in tab 3.  I did a straight COUNTD([Customer ID])  by year and it was way off from attempt 2. For example, there were 323 distinct customer IDs in Central Region in 2010.  But the tab 2 Rolling 12 month distinct in January 2011 showed 414.

Is there anyway to get Tableau to do this?

• 1. Re: Rolling Count Distinct

Hi Joseph,

There is a "Tableau Classics" posted by Joe Mako here:

Re: How to count distinct users on a running period

Please find the attached wb with a slightly adopted version of it.

Yours,

Yuri

• 2. Re: Rolling Count Distinct

Hi Yuriy,

Thanks for the workbook.  That is a very cleaver way to accomplish the rolling count distinct.

It seems there is one limitation on this method though.  It seems like you can only count distinct on one dimension per worksheet.

Is that correct?

For example, you wouldn't be able to do a (Rolling Count Distinct Product Category ) / (Rolling Count Distinct Customer IDs), right?

• 3. Re: Rolling Count Distinct

You're right, mixing two non-additive table calcs on one view

with a different granularity for each one is at least problematic if possible.

I've spent some time trying to build Rolling Count of Distinct [Item]

along with Rolling Count of Distinct [Customer ID] on one view -- no luck. sorry.

Yours,

Yuri

1 of 1 people found this helpful