Max consecutive years for each customer in Tableau

Question

I am trying to find for each customer the Max consecutive years he buys something. I tried to create a calculated field but to no avail. I created two calculated fields

1. Consecutive: if max([Count])>0 then previous_value(0)+1+index()-index() else 0 end

2. max: window_max([Consecutive])

My data looks something like:

Year  |   Customer    |    Count

1996  |      a        |     2

1996  |      b        |     1

1997  |      a        |     1

1997  |      b        |     2

1998  |      b        |     1

So the result would be

a:2

b:3

Answer 1

Use nested table calcs.

The first calc, call it running_good_years, is a running count of consecutive years with sales.

If count(Sales) = 0 then 0 else previous_value(0) + 1 end

The second just returns the max

Window_max(running_good_years)

With table calcs, defining the partitioning and addressing is critical. Partition by Customer, Address by year