Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

A question re Dates in QlikView

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?

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Highlighted
Specialist
Specialist

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.

MonthYear.JPG

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

Highlighted
Specialist
Specialist

Gysbert

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

Thanks

Josh

Highlighted
MVP & Luminary
MVP & Luminary

See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Specialist
Specialist

You are the BEST!

Thank you. That works beautifully.