2 Replies Latest reply on Oct 14, 2015 6:42 AM by Caleb Smith

    Count customers who are active this year but not active within past 'n' years

    Caleb Smith

      This is variation on the "How to count distinct users on running period" problem discussed here:

      How to count distinct users on a running period

       

      From that thread, I found the solution proposed by Joe Mako the closest so far. However, there is a subtle difference between what I'm attempting to do and what that thread is about. That thread is asking "how many distinct users were there in the last n periods." I'm attempting to answer "Of the unique users active within fiscal year "20xx", how many of them HAD NOT been active in the previous 'n' years."

       

      The service provider asking for this report considers a user "new" if they haven't been active within the last 3-4 years. So if a user appeared once 5 years ago, and then showed up again today, they would be considered "new." The reason is because this particular service landscape evolves so quickly that any background knowledge is largely inapplicable if it hasn't been maintained regularly.

       

      In the attached workbook, I've got everything working as in Joe's example. That is "how many unique users are there in a rolling n-year period." However, I need help converting this to "How many users, active during x fiscal year, were innactive in the previous n years."

       

      For example, hopefully this illustrates what I'm going for better:

      Fiscal YearUnique Active in Last 3 yearsUnique Active this year, but inactive in prior three years
      2009257257 (the numbers in this column are largely made up)
      2010512255
      2011754242
      2012795206
      2013841229
      2014888222
      2015982254