topic Re: How to exclude current Month in set analysis in QlikView

How to exclude current Month in set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi all:

I am new to Qlikview and is having a problem here that bothers me for two days already. I don't know how to exclude the current Month in set anaylsis. Here is deal: What I want is finding the minimum value for counts through out 12 month, so obiviously needs to exclude the month we are in otherwise the current month value would always be the smallest.

My expression is:

min({<MonthYear -= {$(=date(today(),'MMM-YYYY'))}>}TOTAL_COUNT)

And this doesn't really work. But if I hard code the month like this:

min({<MonthYear={'*'}-{'JUL-2016'}>}TOTAL_COUNT)

Then IT WORKS. I don't know what to do guys, how to code the current month dynamic ;_;

Thanks in advance

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 15:37:29 GMT

Can you show how your MonthYear field is getting created in the script? Is it truly a date field with dual value or is it a text field? Also look at the below Blog for more help on the topic of dates in set analysis

Dates in Set Analysis

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 15:43:54 GMT

Also, can you check if this -> =Date(Today(), 'MMM-YYYY') gives you JUL-2016 or Jul-2016?

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 15:46:22 GMT

It's a date value, I did somthing exactly like this: : Date(MonthStart(Date),'MMM-YY') as Month. I read the blog and tried some of the expression, the weird thing is that even the expression says okay, when I applied it an error message just appeared saying error in set modifier etc.

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 15:47:09 GMT

It gives me Jul-2016, which is what I have in my MonthYear Field

Re: How to exclude current Month in set analysis

Anonymous — Wed, 20 Jul 2016 15:48:55 GMT

Check the following,

min({$<MonthYear = {'*'}-{"$(=SubField(MonthName(today()),' ',1)&'-'&SubField(MonthName(today()),' ',2))"}>}TOTAL_COUNT)

If this did not work, then please post your sample app, I will provide you the best solution

Regards

Nitin

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 15:49:08 GMT

How about this (all the changes are in red):

Min({<Month -= {"$(=Date(MonthStart(Today()), 'MMM-YY'))"}>}TOTAL_COUNT)

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 15:51:39 GMT

Now I am confused. Is your field Month or MonthYear? You provided this below:

Date(MonthStart(Date),'MMM-YY') as Month

Next is the format MMM-YYYY or MMM-YY?

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 15:52:23 GMT

The expression is okay, it didn't give me an error message this time, but nothing is showing either.

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 15:55:22 GMT

One again, is the field Month or MonthYear. Format is MMM-YYYY or MMM-YY?

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 15:58:46 GMT

Thanks for helping, it doesn't work since the expression is NOT okay, tried but just don't know how to get qlikview to pass ;_;

And sorry I can't upload any file here otherwise I'll need to pass security check...

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 15:59:27 GMT

It's MMM-YYYY, sorry for the confusion

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 16:24:41 GMT

Try this:

Min({<Month -= {"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>}TOTAL_COUNT)

Also change Month to MonthYear if that's what the field name is called

Re: How to exclude current Month in set analysis

tamilarasu — Wed, 20 Jul 2016 16:39:17 GMT

Hi Jingyan,

Try this expression,

Min({<Month -= {"$(=Upper(Date(Today(), 'MMM-YYYY')))"}>}TOTAL_COUNT)

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 17:18:45 GMT

This is what I tried last time. I changed the field name already, not really working for some reason. Thanks tho

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 17:24:22 GMT

Wait NO IT WORKED!!!!!!!!!

Just one more tiny issue, which is the number should be fixed, but everytime when I cliked on some other month the numbers kept changing, and that was the reson why I said it wasn't working before becuase I was clik on the current month. But progress here, let me see if I can fix this.

Thanks a ton!

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 17:24:55 GMT

Assuming this is what you have in script:

Date(MonthStart(Date),'MMM-YYYY') as MonthYear

I would expect this to work:

Min({<MonthYear -= {"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>}TOTAL_COUNT)

Min({<MonthYear ={'*'}-{"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>}TOTAL_COUNT)

Re: How to exclude current Month in set analysis

sunny_talwar — Wed, 20 Jul 2016 17:28:47 GMT

Try this:

Min({<Month = {'*'} - {"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>}TOTAL_COUNT)

Re: How to exclude current Month in set analysis

Wed, 20 Jul 2016 17:31:57 GMT

IT WORKS!!!

MAN YOU ARE A WIZARD THANK YOU BRO

HAPPIEST MOMENT OF THE DAY!!