topic Re: how to finde max data before a selected data in QlikView

how to finde max data before a selected data

marjan_it — Fri, 20 Nov 2020 18:02:30 GMT

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

Re: how to finde max data before a selected data

morgankejerhag — Mon, 09 Nov 2015 19:32:19 GMT

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. !

Re: how to finde max data before a selected data

marjan_it — Tue, 10 Nov 2015 07:46:54 GMT

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?!

Re: how to finde max data before a selected data

morgankejerhag — Tue, 10 Nov 2015 20:00:37 GMT

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')

Re: how to finde max data before a selected data

marjan_it — Sat, 14 Nov 2015 09:29:40 GMT

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.