1 Reply Latest reply on May 6, 2018 9:33 PM by Jennifer VonHagel

    Total count of consecutive yearly order (by customer)

    Yichuan Yu

      Hi, below is data from SuperStore.
      It is the max count of consecutive yearly order of customers.

      So if a customer ordered in 2014, 2016 and 2017, the max count is 2.

      I would like to produce a Bar chart of the total consecutive count of all customers.

      For example,

      1 consecutive yearly order : 20 people

      2 consecutive yearly order : 30 People

      3 consecutive yearly order : 15 People

      4 consecutive yearly order : 10 People

       

      I can only get as far as the below. Can someone help? Thanks!

      Capture.PNG

        • 1. Re: Total count of consecutive yearly order (by customer)
          Jennifer VonHagel

          Hi Yichuan,

           

          This is a brute force method of calculation that works only when 4 years are in play.  This kind of problem should be good for a table calculation, because we can evaluate along any number of years for each customer to get an answer; however, it is difficult to then use a table calculation as a dimension against which we can count distinct customers.

           

          So this calculation just looks at each possible combination of results - again, it works only for data that has up to 4 years to check for.