5 Replies Latest reply: Jan 13, 2016 2:26 PM by Stefan Wühl RSS

    Comparing Two Fields - Only records that exist in both sets

      Hello,


      I have a table with key fields that are as follows:

       

      Primary SecIDiRisk Average LifeYear
      1

      4

      12/31/14
      2412/31/14
      1512/31/15
      316/30/15

       

      I have used a calendar object to create two date variables (vStartDate and vEndDate).

       

      I want to compare a record (e.g. [iRisk Average Life]) across the two dates selected but only display the comparison if there is a match in the unique identifying field, which is [Primary SecID] and there is a difference between the two field in the two dates.

       

      That is I want to only show those records where the 'Primary SecID' has changed from Date 1 to Date 2.

       

      In this instance, if I selected 12/31/14 as date 1 and 12/31/15 as date 2, the results would be as folllows:

       

      Primary SecIDiRisk Average Life - 12/31/14iRisk Average Life - 12/31/15DifferenceYear
      145-1

       

      I am thinking the formula should be something like this...


      sum({$<A_ASOFDATE={vStartDate},[iRisk Average Life] = p({$<A_ASOFDATE={vEndDate}>}[Primary SecID)>}IDATE)


      Please help, thank you!