I have a straight table showing [Max Transaction Date] of [Insurer Policy Number] if [Date], selected from a straight table, lies between [Inception Date] and [Expiry Date], via the expression:
max({$<[Inception Date]={"<$(=max([Date]))"},[Expiry Date]={">$(=max([Date]))"}>}[Transaction Date])
What I now want to show is the corresponding [Vhl 1 Age] to the selected [Transaction Date].
Using Sum(If(Aggr(NODISTINCT Max([Transaction Date]), [Insurer Policy Number]) = [Transaction Date], [Vhl 1 Age])) does return the correct age, but it does this for all policy numbers, even those that shouldn't have been returned if the 1st expression is followed, though the [Transaction Date] is not returned. Selecting supress zero value and supress when null does not help.
Any help would be greatly appreciated.
Ross