Set analysis based on MAX value and IF statement

[lukegilligan]

I am trying to dynamically work out the current rent a tenant should be paying based upon a rent history table and a variable called vReportDate that is set by a calendar object.

I can setup Set analysis to provide the tenants linked MAX rent history table fields, but I want to be able to bring back the RentAmout for MAX value of the startDate before or equal to my vReportDate

For instance:

vReportDate = 16/04//2020

Rent History Table:

Customer.code	Customer.name	HousingRentHistoryRecord.startDate	HousingRentHistoryRecord.rentAmount
100	Bob Jones	19/06/2020	400.00
100	Bob Jones	01/03/2020	390.00
100	Bob Jones	2/08/2019	385.50
100	Bob Jones	25/04/2019	383.70
200	Mary Smith	15/04/2020	320.00
200	Mary Smith	01/01/2020	324.00
200	Mary Smith	13/10/2019	319.00
300	Pete Frank	16/04/2020	187.00
300	Pete Frank	23/09/2019	185.60

The values I need to bring back would be:

Customer.code	Customer.name	HousingRentHistoryRecord.startDate	HousingRentHistoryRecord.rentAmount
100	Bob Jones	01/03/2020	390.00
200	Mary Smith	15/04/2020	320.00
300	Pete Frank	16/04/2020	187.00

Any help would be much appreciated.

[rubenmarin · Accepted solution]

Hi, you can use this expression:

FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)