6 Replies Latest reply: Sep 11, 2014 1:56 PM by Gaurav Jain RSS

    Set analysis - comparing two field

    Steve Zagzebski

      Struggling a bit on some set analysis/expressions...

       

      Data Structure:

       

      ClientYearMonthRenewal Month
      Rate
      ABC2012010490
      ABC2012020495
      ABC20120304100
      ABC20120404125
      ABC20120504130
      ABC20120604130
      ABC20120704130
      ABC20120804130
      ABC20120904130
      ABC20121004130
      ABC20121104130
      ABC20121204130

       

      I need an expression (and the proper dimensions) that would compare the rate of the renewal month to the rate just prior to the renwal month and give the difference. (I would need to take the last two characters of the "YearMonth" field and compare to the "Renwal Month" field. I would be selecting a specific year so that would not be an issue of having multiple years. So the outcome of my formula for just the one client above would be (this would need to be a table that had all clients by their specific renewal month and the difference from the the rate just prior to their renwal month):

       

      Client     Renewal Month     Rate Difference

      ABC      04                         25 (125-100)