Putting months in chronological order using script

[#1]

Hi,

I am trying to order my months table in chronological order instead of alphabetical.  The script I have is the following:

VMO_NEW_OPPORTUNITY:

SQL
select
type1.CODEDESC Type,
OPT9.CODEDESC as Division,
VMO_OPPORTUNITY.IDUSER TM,
count(VMO_OPPORTUNITY.IDUSER) Opportunity_Count,
to_char(VMO_OPPORTUNITY.created,'MON' ) as Month_created,
to_char(VMO_OPPORTUNITY.created,'YYYY' ) as YEAR_created
from ifsapp.vmo_opportunity
left join IFSAPP.VMO_BASE_CODEDESC opt9 on VMO_OPPORTUNITY.OKM_DIVISION=opt9.IDCOD
left join IFSAPP.VMO_BASE_CODEDESC type1 on VMO_OPPORTUNITY.KindofOpp =type1.IDCOD
where idcodprs ='prs.001'
and (OPT9.idlng = 'en' or OPT9.idlng is null)
and (type1.idlng = 'en' or type1.idlng is null)
group by
type1.CODEDESC,
to_char(VMO_OPPORTUNITY.created,'MON' ) ,
to_char(VMO_OPPORTUNITY.created,'YYYY' ),
OPT9.CODEDESC ,
VMO_OPPORTUNITY.IDUSER
;

I know I need to load the months as JAN = 1 etc, but can't remember the SQL for it.  Can someone jog my memory please? 

[Anonymous]

Sara

How about changing this line to :

    to_char(VMO_OPPORTUNITY.created,'MM' ) ,

Best Regards,     Bill

[Anonymous]

Sara

And this one as well :

to_char(VMO_OPPORTUNITY.created,'MM' ) as Month_created,

Best Regards,     Bill

[#4]

Thanks Bill, tried but it brought through the Month JAN FEB etc aswell as numbers!

All I want to be able to do is have the month in JAN FEB etc format but be able to sort it in calendar order.  At the moment it keeps going alphabetically.  Very frustrating.

[#5]

How about using Qlikview to interpret the date in stead of SQL:

LOAD ...

          Month(created) As Month_created

           ...

SQL SELECT VMO_OPPORTUNITY.created

....;

This way Qlikview will show month names, but will internally still use the month number for sorting purposes.

[#6]

Hi Sara, Please use convert all the dates to Month in Qlikview by using Month function, This function stores dual value, so then you can use SORT option in Qlikview with Number.