Evaluate the same expression for 6 previous weeks

[#1]

Hello !

I am trying to do something but I have no idea for the moment.

I have a very simple expression which returns a rate :

ex : sum(Customer_Rate)

I have a goal for the rate. So I compare it each week.

if(sum(Customer_Rate)<$(goal),'Good','Alert')

When I use it in a crosstable I have the following result :

Customer	Week 48 (current)	Week 47	Week 46	Week 43	Week 42	Week 41
Customer A	Good	Good	Alert	Alert	Alert	Alert
Customer B	Alert	Alert	Good	Alert	Alert	Alert
Customer C	Alert	Alert	Alert	Alert	Alert	Alert

I am trying to evaluate the number of consecutive Alert if the current week is in alert (from 0 alerts to 6+ alerts).

In my example :

Customer A : 0

Customer B : 2

Customer C : 6+

It must stay dynamic, so if I select week 46 :

Customer A : 6+

Customer B : 0

Customer C : 6+

As someone an idea ?

Is it clear ?

Thanks for your help !

BR,

Benoit