How to exclude current Month in set analysis

[#1]

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

[sunny_talwar]

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

[sunny_talwar]

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

[#4]

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.

[#5]

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

[Anonymous]

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

[sunny_talwar]

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

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

[sunny_talwar]

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?

[#9]

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

[sunny_talwar]

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