topic Re: Max Month in QlikView

Max Month

Fri, 28 Mar 2014 13:14:54 GMT

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

alexandros17 — Fri, 28 Mar 2014 13:18:42 GMT

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):

Load

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

MarcoWedel — Fri, 28 Mar 2014 13:20:05 GMT

=month(max(month))

regards marco

Re: Max Month

Anonymous — Fri, 28 Mar 2014 13:20:53 GMT

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

Fri, 28 Mar 2014 13:21:22 GMT

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

MK_QSL — Fri, 28 Mar 2014 13:43:39 GMT

Try below in text box...

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

Re: Max Month

DavidFoster1 — Fri, 28 Mar 2014 13:47:50 GMT

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

Re: Max Month

Fri, 28 Mar 2014 14:04:35 GMT

use

=month(max(month))

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

Vinay