Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jlampard40
Contributor III
Contributor III

Max Date not equal to...!

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

Labels (2)
5 Replies
chriscammers
Partner - Specialist
Partner - Specialist

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

jlampard40
Contributor III
Contributor III
Author

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

jlampard40
Contributor III
Contributor III
Author

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?

chriscammers
Partner - Specialist
Partner - Specialist

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.

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.