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

    Rolling Count Distinct

    joseph.bertram

      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?