Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)

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 

1 Answer

0 votes
by (47.2k points)
  • Use nested table calcs.

  • Call the first calculated field running_good_years which 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

Browse Categories

...