topic Re: SELECT THE LAST MONTH AND YEAR in QlikView

SELECT THE LAST MONTH AND YEAR

Anonymous — Wed, 16 Nov 2011 21:07:16 GMT

Hi guys,

How do I get data only for the last month in expression? My variable month and year is a string.

I want to get something like that:

count( CONDITION SELECTED THE LAST MONTH AND YEAR , Value)

Thank in advance,

Re: SELECT THE LAST MONTH AND YEAR

johnw — Wed, 16 Nov 2011 21:30:22 GMT

I'm not sure what you mean by "My variable month and year is a string."

I would normally have my Month field defined like this:

date(monthstart(Date),'MMM YYYY') as Month

In that case, probably this:

count({<Month={'$(=date(max(Month),'MMM YYYY'))'}>} Value)

Re: SELECT THE LAST MONTH AND YEAR

Anonymous — Wed, 16 Nov 2011 21:54:09 GMT

Thank you again . It works!!!

The format is underline 'MMM YYYY'. Do you know why?

Regards,

Re: SELECT THE LAST MONTH AND YEAR

johnw — Wed, 16 Nov 2011 22:01:17 GMT

So in the expression editor, it's underlining the 'MMM YYYY' as bad, but the expression works anyway? If so, my guess is that the editor is getting confused by the use of single quotes, and it thinks that '$(=date(max(Month),' is a quoted string, and that '))' is a quoted string. With that interpretation, MMM YYYY is just sitting in the middle of the expression where it thinks a comma belongs instead. So it highlights it. It's wrong about it being an error, but I can understand how it got confused.

When QlikView processes this, it will process the $(...) part before even looking at anything else. At that point, the inner single quotes will be removed, and instead we'll have something like JAN 2011. The single quotes around it will then work normally.