how to finde max data before a selected data

[marjan_it]

PersonCode	RegisteredDate
1	11/9/2015
1	10/4/2015
2	7/4/2015
2	11/8/2015
2	23/7/2015

I have a table like this.and I have registeredDate in Listbox. when I Clike on a date in registeredDate I want to find  maximum Date of personcode that is lower or equal tahan the selected registered Date.

for example when I clik on 11/8/2015 return 10/4/2015 for personcode=1 and return 11/8/2015 for personcode=2

[morgankejerhag · Accepted solution]

Hello,

Look at the attached example. It uses set analysis and dollar sign expansion to go outside of the current selection on the date. When using dates in set analysis I find it easiest to have a numeric version of the field. The table to the left with the source data is in another alternate state and is not affected by the changes in the field to the right. !

[marjan_it]

Thank you for your complete answer. It is work by GREGORIAN_DATE. but I have another problem. I shoud change my date to persian date and in selective listbox use persian date. when I change date to persian it is not work again.

for changin to persian I try both way use of m2sh function and use of a table with 2 field GREGORIAN_DATE and its equivalent  persian date. but it does not work. what shuld I do now?!

[morgankejerhag]

Sorry, I have never worked with Persian dates. If the Persian date is in another field you probably need to remove that selection in the set analysis as well:
date(max({<PersianPregisteredDate=, RegisteredDate=, RegisteredDateNum={"<= $(=RegisteredDateNum)"}>} RegisteredDate),'D/M/YYYY')

[marjan_it]

Thank you for your complete answer.

Now how can I add some additional Condition to the expression, for example count person code that in addition to previous condition,  their age is less than 30 or something like this.