7 Replies Latest reply: Mar 28, 2014 10:04 AM by Vinay Naran

# Max Month

Hi everybody,

I have Year Field as 2010,2011,2012 and

Month Field as JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

For Getting Maximum Year , I have used =Max(Year). Its working fine and returns 2012

But for getting Maximum Month , I have used =Maxstring(Month). It not returns 'DEC' , Instead it returns 'SEP'

I want to get the maximum month as DEC.

Kindly help me to sort out this issue.

Regards,

Sivasu

• ###### Re: Max Month

If you need to use months as numerical values (Max for example) I suggest you to use month as year + month so, if you have a date (myDate):

weekyear(myDate)*100 + month(myDate) as month,

monthname,

....

In this way you will have a numerical value for months that will help to find max

Hope it helps

• ###### Re: Max Month

=month(max(month))

regards marco

• ###### Re: Max Month

maxstring is sorting by alphabets...so S is the last alphabet in the month's list. so SEP is being returned.

you need to use dual function here...straight from the help section. look up dual in help and copy that example into your script. just change the field name to match yours and use months like JAN, 1; FEB, 2; etc. then you can call max(Month) and it will return DEC.

straight from help section:

load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep

Monday,0

Tuesday,1

Wednesday,2

Thursday,3

Friday,4

Saturday,5

Sunday,6 ];

load Date, weekday(Date) as DayOfWeek from afile.csv;

The script example will generate a field DayOfWeek
with the weekdays written in clear text. QlikView will for all purposes regard
the field as a numeric field.

then use Marco's expression to get the string representation of the max month instead of the number.

• ###### Re: Max Month

Hi Sivasu,

If this month and year are coming from some date field then instead of taking max(Month) use Month(max(date)).

This will give you latest date. But if you want max month then load one inline table with two fields i.e. Month and Month_Number and associate with this table and then take max(Month_Number) you will get dec.

Regards

Suhas Gupta

• ###### Re: Max Month

Try below in text box...

=SubField('\$(MonthNames)', ';' , Max(Month))

• ###### Re: Max Month

If you have to have text-style months in your data then always use DUALs for Months.

• ###### Re: Max Month

use

=month(max(month))

you can't use maxstring because it return maximum value by alphabets  and SEP start with S.

Vinay