Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
=month(max(month))
regards marco
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.
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
Try below in text box...
=SubField('$(MonthNames)', ';' , Max(Month))
If you have to have text-style months in your data then always use DUALs for Months.
use
=month(max(month))
you can't use maxstring because it return maximum value by alphabets and SEP start with S.
Vinay