topic Re: Max Date not equal to...! in QlikView

Max Date not equal to...!

jlampard40 — Sat, 16 Nov 2024 02:23:42 GMT

Hi guys. I have a field called [AD_Request_Date] and I want Qlikview to show me (in a text box) what the maximum date is, for that field. However, there is a date of 01/01/2099 which keeps coming up in the data which is used to signify a date which has not been entered. I would like to therefore ignore that data, and just give me the max date in the field. Can you help? Thanks

Re: Max Date not equal to...!

chriscammers — Wed, 25 Sep 2019 14:29:37 GMT

Max({<[AD_Request_Date] -= {"01/01/2099"}>}[AD_Request_Date])

Or just in case there are other bogus dates in your data you might want to try this one...

Max({<[AD_Request_Date] = {"$(='<=' & Today())"}>}[AD_Request_Date])

Re: Max Date not equal to...!

jlampard40 — Wed, 25 Sep 2019 15:28:38 GMT

Thanks. However, it's returning a number of '72686'...? Any ideas why?

Re: Max Date not equal to...!

jlampard40 — Wed, 25 Sep 2019 15:34:24 GMT

OK - I've used this:

=Date(max({<[AD_Request_Date] -= {'01/01/2099'}>}[AD_Request_Date]))

But it's still returning 01/01/2099 as the max date...! Any ideas?

Re: Max Date not equal to...!

chriscammers — Wed, 25 Sep 2019 20:57:23 GMT

sometimes qlik is a little tricky with dates

=Date(max({<[AD_Request_Date] -= {'$(=Date(Num('01/01/2099)))'}>}[AD_Request_Date]))

If that does not help then I would suggest applying some date functions on your AD_Request_Date in the load script so it uses the default Qlik date format. For example you probably want to do Date(Floor(Num(AD_Request_Date))) as AD_Request_Date in your load script.

Re: Max Date not equal to...!

Brett_Bleess — Thu, 03 Oct 2019 21:29:59 GMT

James, did Chris' last post work for you? If so, please be sure to use the Accept as Solution button on his post to give him credit for the help and let others know it worked. If you did something different, consider posting that and then mark that post as the solution. If you are still working on things, leave an update.

Here are a couple of links from the Design Blog area that may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

Regards,
Brett