Hello All, We are trying to find out if there are any amounts(not zeroes) values appearing consecutively for three or more months(Jan-Sep).. I have tried various ways but couldn't generate the output. Below the example: Service_PortionJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptember5000218.693.860.79110021.940050004117.77055.665.67060.92051.905000545.87054.29039.58021.2700500062.7204.825.6722.44016.092.262.95000700004.93.43.63.70 Service_PortionJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptember5000218.693.860.79110021.9400500062.7204.825.6722.44016.092.262.95000700004.93.43.63.70 Please help. Best Regards, Scheruku.

Consecutive month amount not blank for three consecutive months or more

Hello All,

We are trying to find out if there are any amounts(not zeroes) values appearing consecutively for three or more months(Jan-Sep)..

I have tried various ways but couldn't generate the output. Below the example:

Service_Portion	January	February	March	April	May	June	July	August	September
50002	18.69	3.86	0.79	11	0	0	21.94	0	0
50004	117.77	0	55.6	65.67	0	60.92	0	51.9	0
50005	45.87	0	54.29	0	39.58	0	21.27	0	0
50006	2.72	0	4.8	25.67	22.44	0	16.09	2.26	2.9
50007	0	0	0	0	4.9	3.4	3.6	3.7	0

Service_Portion	January	February	March	April	May	June	July	August	September
50002	18.69	3.86	0.79	11	0	0	21.94	0	0
50006	2.72	0	4.8	25.67	22.44	0	16.09	2.26	2.9
50007	0	0	0	0	4.9	3.4	3.6	3.7	0

Please help.

Best Regards,

Scheruku.

Preparation

Accepted answers

danrh

I'd suggest using a Cross Tab and then a Multi-Row Formula:

Once you have all your months in a single column do a running count of months with >0, then filter down to just the records that hit 3 or more. Join it back to your data to get the original records and you should be good to go.