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: 
maxim1500
Partner - Creator
Partner - Creator

Date functions in set analysis

Hello,

I am trying to filter out a dataset to keep only the last day of the current year (based on my max snapshot date selection).

I am expecting something like this to work, based on what I saw on this forum:

[Snapshot Date] = {"$(=MonthEnd(YearStart(max([Snapshot Date]))))"}

If I do =MonthEnd(YearStart(max([Snapshot Date]))) in a KPI, I get the right date.

Also, If I execute this function, it works:

Max({<[Snapshot Date] = {"$(=min([Snapshot Date]))"}>}[Snapshot Date])

But as soon as I add another function (like MonthEnd, AddMonths, ...), I only get "-" as a result.

Any idea?

Thank you!

1 Solution

Accepted Solutions
sunny_talwar

I see what the issue is, try this now:

[Snapshot Date] = {"$(=Num(Floor(MonthEnd(YearStart(max([Snapshot Date]))))))"}


MonthEnd gives a timestamp such as 01/31/2016 11:59:59 PM which won't match you date. By adding a Floor you will get ride of the time portion

View solution in original post

4 Replies
sunny_talwar

Is your Snapshot Date formatted as a number? If it is, try this:

[Snapshot Date] = {"$(=Num(MonthEnd(YearStart(max([Snapshot Date])))))"}

or if it is date, then this may be:

[Snapshot Date] = {"$(=Date(MonthEnd(YearStart(max([Snapshot Date]))), 'DateFieldFormatHere'))"}

maxim1500
Partner - Creator
Partner - Creator
Author

[Snapshot Date] is a number when I output it in a kpi with auto format.

Max({<[Snapshot Date] = {"$(=Num(MonthEnd(YearStart(max([Snapshot Date])))))"}>}[Snapshot Date]) -> Doesn't work

Max({<[Snapshot Date] = {"$(=Num(MonthEnd(max([Snapshot Date]))))"}>}[Snapshot Date]) -> Doesn't work

Max({<[Snapshot Date] = {"$(=Num(max([Snapshot Date])))"}>}[Snapshot Date]) -> works fine.

MonthEnd should work just fine as all dates are end of month. Max(date) = '2013-12-31' and '2013-01-31' is there.

As a mather of fact:

Max({<[Snapshot Date] = {"$(=Num(min([Snapshot Date])))"}>}[Snapshot Date])   returns 41305 (2013-01-31)

sunny_talwar

I see what the issue is, try this now:

[Snapshot Date] = {"$(=Num(Floor(MonthEnd(YearStart(max([Snapshot Date]))))))"}


MonthEnd gives a timestamp such as 01/31/2016 11:59:59 PM which won't match you date. By adding a Floor you will get ride of the time portion

maxim1500
Partner - Creator
Partner - Creator
Author

I would never have found that one alone! Thx!