Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rbudgeon
Partner - Contributor
Partner - Contributor

Find Last Value as at Report To Date

Hi all. I'm creating a report where I need to bring in the last charge amount.

An example of my data is:

RentStartDate              RentAmt

01/01/2020                    300.00

01/09/2020                    360.00

01/10/2020                    260.00

01/11/2020                    270.00

 

At present I'm using:

Sum({<selectedToDate = {'$(=max(RentStartDate))'}>} RentAmt)

Where selectedToDate is a date variable that a user can select on the report.

Where RentStartDate is the start date of the rent record and RentAmt is the rent charge amount that I want to bring in.

I've selected 02/10/2020 on my report which I would expect to return 260.00, however it is bringing through 1190 which is the total of all of the RentAmts.

Any help would be appreciated.

 

1 Reply
Vegar
MVP
MVP

You can not make a modifier for a variable, you can only do it for fields. Consider set analysis as a way to create invisible selection in your data model in order to get a correct calculation. 

I'll try to rewrite you expression as I understood you, correct me if you think that I missunderstood you. 

Sum({<RentStartDate={'$(=maxstring({<RentStartDate={" <=' $(selectedToDate)'} ">} RentStartDate))' } RentAmt)