Problem in set analysis to find latest value

[sagarjagga]

Hi All,

I need to show latest rating in selected date range for given users.Below is the sample data.

User Name	Rating	update date
A	1	10/11/2018
b	1	10/15/2018
c	2	11/1/2018
d	0	11/1/2018
A	2	11/5/2018
c	3	11/6/2018

I have field name date which contains all the dates in a calendar.

so if a select a date as 10/16/2018 , it should return me the latest rating of users. the result should be like

User Name	Rating
A	1
b	1

and if a select a date as 11/07/2018 . the table should return 

User Name	Rating
A	2
b	1
c	3
d	0

as for A and c the latest rating is 2 and 3 respectively.

Regards,

Sagar

[Gysbert_Wassenaar]

Perhaps like this: max({<[update date]= {"<=$(=max([update date]))"}>}Rating)

Or perhaps a better solution is FirstSortedValue({<[update date]= {"<=$(=max([update date]))"}>}Rating, -[update date])