Consecutive Monthly Sales

[RohanRoy]

Hi

I have a scenario where there is data for 12 months sales for each Entity Where in I need to consider only the first three consecutive months sales. i.e., if Jan Feb March is having continuous sales then output only Jan Feb March Numbers, if no sales then move to next three consecutive month sales. it could be Feb March Apr or Apr May Jun or Aug Sep Oct or Oct nov Dec likewise any three also not to be considered if no three consecutive month sales.

Input :

Entity ID	Month	Sales
225	January	0
225	February	700
225	March	0
225	April	400
225	May	0
225	June	700
225	July	300
225	August	200
225	September	200
225	October	300
225	November	0
225	December	500
501	January	0
501	February	0
501	March	1200
501	April	0
501	May	0
501	June	0
501	July	1000
501	August	900
501	September	0
501	October	300
501	November	0
501	December	2000
202	January	2000
202	February	6000
202	March	2000
202	April	3000
202	May	4000
202	June	400
202	July	300
202	August	300
202	September	100
202	October	5000
202	November	400
202	December	5000
170	January	400
170	February	100
170	March	0
170	April	200
170	May	500
170	June	500
170	July	1000
170	August	1000
170	September	2000
170	October	2000
170	November	400
170	December	400

Expected Output

Entity ID	Month	Sales
225	June	700
225	July	300
225	August	200
202	January	2000
202	February	6000
202	March	2000
170	April	200
170	May	500
170	June	500

[TimN]

This seems to work.  Have not tried with more data though..

[RohanRoy]

Thanks for the quick reply. But that's not working. Missing the consecutive months,

[RohanRoy]

Thanks for the quick reply. But that's not working. Missing the consecutive months,

[TimN]

I think you just need to get the If Statement right in the Multi-Row tool.  I tried:

IF [Sales] > 0 and [Row+1:Sales]> 0 and [Row+2:Sales]> 0 THEN "Y"
ELSE
"N" ENDIF

Seems to be better but not sure if this will work for multiple years...

[cjaneczko]

There is likely an easier way, but this seemed to work. 

[RohanRoy]

Thanks Tim. That is working for the data which i shared but when I implemented on other entity, its not working.

Feb Mar Apr should be the output

101	January	0
101	February	800
101	March	300
101	April	300
101	May	0
101	June	500
101	July	300
101	August	600
101	September	100
101	October	700
101	November	0
101	December	600

[AndrewDMerrill]

Here is a variant that should work for all test cases: