Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set analysis based on MAX value and IF statement

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.codeCustomer.nameHousingRentHistoryRecord.startDateHousingRentHistoryRecord.rentAmount
100Bob Jones19/06/2020400.00
100Bob Jones01/03/2020390.00
100Bob Jones2/08/2019385.50
100Bob Jones25/04/2019383.70
200Mary Smith15/04/2020320.00
200Mary Smith01/01/2020324.00
200Mary Smith13/10/2019319.00
300Pete Frank16/04/2020187.00
300Pete Frank23/09/2019185.60

 

The values I need to bring back would be:

Customer.codeCustomer.nameHousingRentHistoryRecord.startDateHousingRentHistoryRecord.rentAmount
100Bob Jones01/03/2020390.00
200Mary Smith15/04/2020320.00
300Pete Frank16/04/2020187.00

 

Any help would be much appreciated.

1 Solution

Accepted Solutions

Hi, you can use this expression:

FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)

View solution in original post

1 Reply

Hi, you can use this expression:

FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)

View solution in original post