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

    Tyler Gregory


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


      Primary SecIDiRisk Average LifeYear




      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


      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!