Find Last Value as at Report To Date

rbudgeon — Wed, 14 Oct 2020 01:23:07 GMT

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.

Re: Find Last Value as at Report To Date

Vegar — Wed, 14 Oct 2020 04:36:20 GMT

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)

topic Re: Find Last Value as at Report To Date in App Development

Find Last Value as at Report To Date

Re: Find Last Value as at Report To Date