Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have dates formatted in MMM-YY. Why won't the following set analysis work? It gets the results, but appears to be calculating as < not <= (doesn't include maximum month).
{<[Encounter Month/Year]={"<=$(=max([Encounter Month/Year]))"}>}
The only way I have figured out to get the correct value is to use < and add 30 days
{<[Encounter Month/Year]={"<$(=DATE(max([Encounter Month/Year])+30,'MMM-YY'))"}>}
However, strangely enough, >= appears to work.
Thanks!
The field is formatted as MonthYear, but does it also use MonthStart to nullify the effect of date? How exactly do you create Encounter Month/Year field in the script?
date(monthstart([Encounter Month/Year Tmp])+14,'MMM-YY') AS [Encounter Month/Year]
where Encounter Month/Year Tmp is:
LOAD *INLINE [
Encounter Month/Year Tmp
02/15/2017
03/15/2017
04/15/2017
05/15/2017
06/15/2017
07/15/2017
08/15/2017
09/15/2017
10/15/2017
11/15/2017
12/15/2017
01/15/2018
02/15/2018
03/15/2018
04/15/2018
05/15/2018
06/15/2018
07/15/2018
08/15/2018
09/15/2018];
Is there a reason for you to add +14? I am not sure I understand the use of that?
Ya know, that's a great question! I might have started with dates different dates or something wasn't working and I added that and it started working?
Can you try this
Date(MonthStart([Encounter Month/Year Tmp]),'MMM-YY') as [Encounter Month/Year]
and then this
{<[Encounter Month/Year] = {"<=$(=Date(MonthStart(Max([Encounter Month/Year])), 'MMM-YY'))"}>}
Given I need this at the 15th of the month, should these combinations have worked together? Only difference from yours is the +14.
Date(MonthStart([Encounter Month/Year Tmp])+14,'MMM-YY') as [Encounter Month/Year]
{<[Encounter Month/Year] = {"<=$(=Date(MonthStart(Max([Encounter Month/Year]))+14, 'MMM-YY'))"}>}
This resolves but the answer is not what it is expected - it is providing the answer for < not <=
Could be a bug in Sept 2018 onwards. We too faced issue with MMM-YY format in set analysis specifically after migration to Sept 2018 release. See the image attached, it doesn't make sense.
I just checked a listbox (outside of set analysis) and you are right! Looks like >= works but <= does not work correctly. Do you know if Qlik is aware of this bug?