Consecutive month below productiviy standards

[#1]

Hi, I have a chart (straight table) : sample below and attached. I am trying to add another expression after productivity %, determines number of consecutive months that an employee has been below the productivity standards (current standard is < =20%). above 20% is below standards.  Any idea?

Month	Enployee	Productivity %
07/01/12	Amy	10
07/01/12	John	22
07/01/12	Faith	20
06/01/12	Amy	10
06/01/12	John	21
06/01/12	Faith	22
05/01/12	Amy	25
05/01/12	John	20
05/01/12	Faith	23
04/01/12	Amy	10
04/01/12	John	11
04/01/12	Faith	12
03/01/12	Amy	56
03/01/12	John	17
03/01/12	Faith	20

[swuehl]

Maybe like this?

INPUT:

LOAD Month,

     Enployee as Emp,

     [Productivity %] as Prod

FROM

.\productivity.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD *, if(Prod > 20, 0 ,AutoNumber(recno(),GroupID)) as ConsecutiveMonth;

LOAD *, if(Prod > 20, Null(), if(peek(Emp) <> Emp or peek(Prod) > 20 ,recno(), peek(GroupID) )) as GroupID resident INPUT

order by Emp, Month;

drop table INPUT;

[#3]

Thanks,

I am not sure if that is working correctly, I want when I click on July for example for number of consecutive month to read consecutive months from July where the PROD is greater than 20%. I am not sure how the GROUPID works in your load

[swuehl · Accepted solution]

Ok, I think I misunderstood your request.

Try replacing all occurrences of > 20 with <=20