A question re Dates in QlikView

[jduenyas]

Hi All

I have in my calendar a field, MonthYear, that contains a month and a year such as: Jan 2012, Feb 2012...May 2013 Jun 2013 etc.
I use this field to select "rolling months" such as last 12 month etc.

When selecting any number of values in this field the relevant data is being displayed in charts and tables.

However, in one particular table I wish to show only the data which corresponds to the last month of the selectd range MonthYear. So if I select Oct 2013 thru Apr 2012, I wiould like to show only data from Apr 2013.

For that I have created this expression:
=if(num(month(SurveyDate))  =  num(month(MaxString(MonthYear))),SurveyDate) (SurveyDate is a date field in my data table)

However, that does not work. The returned are ALL records.

If I replace num(month(MaxString(MonthYear))) with the number 4 (for Apr) it works:
=if(num(month(SurveyDate))  =  4, SurveyDate)  -- this works fine.

Also, if I replace num(month(MaxString(MonthYear))) with num(month(now())) it works:
=if(num(month(SurveyDate))  =  num(month(now())), SurveyDate)  -- this works fine but the data returned is of the current month (Jun as of today)

The field MonthYear is created in my calendar like so:

Load ...
...
MonthName(TempDate) AS MonthYear
...
Resident TempCalendar;

In a textbox, entering the formula num(MaxString(MonthYear)) returns, as expected, a numeric value of the last month selected which tells me that the value in MonthYear is indeed a date datatype

My question then is: Why num(month(MonthYear)) does not return a value that can be compared to the date field in my data?

[Gysbert_Wassenaar]

if(month(SurveyDate)=month(MonthYear),SurveyDate) should work. Using the maxstring won't since it returns the maximum text value. April begins with A, March begins with M, M is larger than A in string comparison so Mar-2012 is larger than Apr-2012.

If this didn't help you, please read Preparing examples for Upload - Reduction and Data Scrambling

[jduenyas]

Thank you Gysbert for you reply.

However, if I select other months Jun 2012 through May 2013, the largest Alphabetic month should be Oct but in a text box as the snapshot shows, the Last month selected, May 2013 is selected, as expected. None-the-less per my expressions =if(num(month(SurveyDate))  =  num(month(MaxString(MonthYear))),SurveyDate) does not return records for only May but rather of ALL records.

In a separate reply I will attempt to load sample data as my application is VERY large and will not fit in here

Once again, thank you

Josh

[jduenyas]

Gysbert

I hope I uploaded the sample file correctly. Please advise if not

Thanks

Josh

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[jduenyas]

You are the BEST!

Thank you. That works beautifully.