Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi, you can use this expression:
FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)
Hi, you can use this expression:
FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)