Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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?

Highlighted
Partner
Partner

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.

Highlighted
Digital Support
Digital Support

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.