Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
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

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.6k users

Browse Categories

...