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

[#2]

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

[#3]

It's MMM-YYYY, sorry for the confusion

[sunny_talwar · Accepted solution]

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

[tamilarasu]

Hi Jingyan,

Try this expression,

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

[#6]

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

[#7]

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!

[sunny_talwar]

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)

or

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

[sunny_talwar]

Try this:

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

[#10]

IT WORKS!!!

MAN YOU ARE A WIZARD THANK YOU BRO

HAPPIEST MOMENT OF THE DAY!!