Actually, I wanted a different output. I want to count the number of Top 20 that each customer has come across. In the sample output, I have only taken Top 5, 10 customers and 2 weeks for easy understanding.
Consider the below screenshot
As you can see Customers are weekly ranked based on the Total Amount (A.k.a AmountTotal in the original post) called Rank. Now I want a column that will check whether the customer has been in Top 5 or not. If it has been in Top 5 in week 1 and also in week 2, then the customer has been in Top 5 for 2 weeks consecutively (i.e the count).
However, it may happen that customer may lose its Top 5 streak (i.e Customer coming in Top 8 instead for a particular week). If it does, then the Top 5 Streak for that customer should reset.
Similarly, in the original problem, I need to do the same. I need to find customers that are in Top 20 weekly and the number of times they are coming Top 20 for consecutive weeks.