Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kmstephenson
Contributor

<= not working in set analysis

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!

13 Replies

Re: <= not working in set analysis

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?

Highlighted
kmstephenson
Contributor

Re: <= not working in set analysis

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

Re: <= not working in set analysis

Is there a reason for you to add +14? I am not sure I understand the use of that?

kmstephenson
Contributor

Re: <= not working in set analysis

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?

Re: <= not working in set analysis

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'))"}>}
kmstephenson
Contributor

Re: <= not working in set analysis

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 <=

Digvijay_Singh
Honored Contributor III

Re: <= not working in set analysis

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. 

Re: <= not working in set analysis

Would you be able to share a sample to check this out? Although, there is no decimals here, but this could be a rounding type issue. Without a sample, it would be difficult to see it... but if you can provide a sample... I am more than happy to take a look at this
kmstephenson
Contributor

Re: <= not working in set analysis

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?