Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
Thanks. However, it's returning a number of '72686'...? Any ideas why?
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?
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.
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