Consecutive month amount not blank for three consecutive months or more

[Scheruku]

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.

[danrh · Accepted solution]

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.

[MarqueeCrew · Accepted solution]

@Scheruku,

Here's my solution:  Transpose & count rows where non-zero.  Find your keys and filter (using a join).

Cheers,

Mark

[MarqueeCrew]

Just like @danrh!

We must be right :)

[Scheruku]

Amazing! Thanks much for your help.

[Scheruku]

Thank you so much for the help!