Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lukegilligan
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
rubenmarin

Hi, you can use this expression:

FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)

View solution in original post

1 Reply
rubenmarin

Hi, you can use this expression:

FirstSortedValue(HousingRentHistoryRecord.rentAmount, -HousingRentHistoryRecord.startDate)

View solution in original post