Help with an expression (not sure how to name what i'm searching for)

[#1]

Dear all,

There are 3 fields to consider. 1. Dossier_schade_sleutel. (this will be among others a dimension in my bar chart. Month-Year, will be as well). rnk_reservering or reservering_ve, those fields show the rank/version of the line (the dossier_schade_sleutel). The date field and an amount field which falls out of the screencap. The result i need is when I select a date from Datum_aangemaakt_reser I want to see the amount from the highest version (reservering_ve) or the lowest rank (rnk_reservering) available on the dossier_schade_sleutel before that date. The data is loaded, so i think I need to solve this in my expression. I hope someone can help me out. I have uploaded the tabel that is considered. It would mean a great deal to me if anybody could help me. If this isn't clear i could open a teamviewer session or call.

Greetings, Roy

[Gysbert_Wassenaar]

Can you upload a small document? If you're worried about confidential information see this document: Preparing examples for Upload - Reduction and Data Scrambling

[#3]

Hoi Gysbert,

Thanks for your reply, i've added an example. If i can clarify the issue more please ask. I hope you can help me.

Greetings,

Roy

[Gysbert_Wassenaar]

See attached qvw. Are those the numbers you expect? If not, can you give an example?

[#5]

Hi Gysbert,

I scrambled the numbers so I had to check in my Dashboard. I came to the conclusion that the highest rank before the date was always showing, it should be the lowest rank. Also when i select a date the selection should be ..DATE (up-to date) numbers are not always showing because the filter excludes dossier_schade_sleutel. I uploaded a new document which maybe clarifies the situation more. I find it hard to explain. Given your name I assume you are dutch, so maybe I could give you a call? Thanks a lot already for your help.

Best Regards,

Roy Covalenco

[Gysbert_Wassenaar]

I think this expression should do what you're asking for:

FirstSortedValue({<Datum_aangemaakt_reservering={'<=$(=max(Datum_aangemaakt_reservering))'}>}Bedrag_reservering,

rnk_reservering)

But the firstsortedvalue can only return a value if there is only one value of Bedrag_reservering for a value rnk_reservering. For dossier 990000289-2 this is not the case. The dates 13-10 and 17-10 both have rnk_reservering value 1, so there are two values Bedrag_reservering for that dossier. Which one is the correct one?