topic Re: <= not working in set analysis in QlikView

<= not working in set analysis

kmstephenson — Wed, 19 Dec 2018 19:20:47 GMT

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!

Re: <= not working in set analysis

sunny_talwar — Wed, 19 Dec 2018 19:26:56 GMT

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?

Re: <= not working in set analysis

kmstephenson — Wed, 19 Dec 2018 19:30:08 GMT

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

sunny_talwar — Wed, 19 Dec 2018 19:32:00 GMT

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

Re: <= not working in set analysis

kmstephenson — Wed, 19 Dec 2018 19:41:05 GMT

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

sunny_talwar — Wed, 19 Dec 2018 19:43:49 GMT

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

Re: <= not working in set analysis

kmstephenson — Wed, 19 Dec 2018 19:49:08 GMT

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

Re: <= not working in set analysis

Digvijay_Singh — Wed, 19 Dec 2018 20:04:57 GMT

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

sunny_talwar — Wed, 19 Dec 2018 20:09:13 GMT

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

Re: <= not working in set analysis

kmstephenson — Wed, 19 Dec 2018 20:09:53 GMT

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?

Re: <= not working in set analysis

Digvijay_Singh — Wed, 19 Dec 2018 20:18:36 GMT

We have raised the case which is in progress. I still somewhere feel rounding can be a cause but all my tests says its a bug because same app was working fine before Sept 2018 migration, but now failing after reload in Sept 2018 with no change at all.

Re: <= not working in set analysis

Digvijay_Singh — Wed, 19 Dec 2018 20:21:42 GMT

Strange thing is, Copy of app reloaded in last version works fine but fails(<= works like <) when reloaded in new version.

Re: <= not working in set analysis

kmstephenson — Wed, 19 Dec 2018 20:22:01 GMT

Have you noticed the same bug that was mentioned in this thread? Are you on Sep 2018 version?

Re: <= not working in set analysis

sunny_talwar — Wed, 19 Dec 2018 20:25:23 GMT

@kmstephenson wrote:
Have you noticed the same bug that was mentioned in this thread? Are you on Sep 2018 version?

Is this question for me? If it is, I don't have Sept 2018 version, but I can download it and check. But again, without a sample... updating to Sept2018 won't really matter.

But you don't have to need confirmation from me. If both of you are having the same issue, then it might very well be a bug and you can either downgrade to an older version or wait for a fix to come out and use an updated version of the expression you proposed.

Best,
Sunny