How to compare data across rows

[#1]

I am trying to do something which I feel should be very easy but I am really struggling with.

I have a data source simillar to the following

[Contract] , [ArrearsFlag], [Effective Date],[Arrears Amount]

A1234                  N           20111101           1000

A1234                  Y           20111102           2000

A1234                  Y           20111103           3000

A1235                  N           20111103           4000

In a list box I  want the user to be able to select a date .g. 20111103 & I want it to display details in a chart for all Contracts where the ArrearsFlag is set to Y

(so far so good !!) .

I want to display the current details e.g. Dimensions = [Contract] & [Effective Date] &  [Arrears Amount]  thus in the example above if I select 20111103 I want to see A1234 , 20111103, 3000

However the tricky bit is that I also want to see when the flag changed from N to Y (or the earliest date if its always been Y)

Thus A1234 , 20111103, 3000 and 20111102 as the Date changed.

Any ideas gratefully appreciated !!

[swuehl · Accepted solution]

Maybe like attached?

I assumed that the flag won't change back to N after it has reached Y, is this valid? (If it is not, I think that makes things quite complicated).

Regards,

Stefan

[#3]

Thanks Stefan,

The example was very useful - as well as providing the set analysis formulae I found it interesting how you limited the chart to the relevant data which was neater than the way I had been doing it - so two solutions for the price of one !! thanks.