2 Replies Latest reply: Nov 29, 2011 6:15 AM by brian hedges RSS

    How to compare data across rows

      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 !!