Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Putting months in chronological order using script

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? 

5 Replies

Re: Putting months in chronological order using script

Sara

How about changing this line to :

    to_char(VMO_OPPORTUNITY.created,'MM' ) ,


Best Regards,     Bill


Re: Re: Putting months in chronological order using script

Sara

And this one as well :

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

Best Regards,     Bill


Not applicable

Re: Putting months in chronological order using script

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.

Not applicable

Re: Putting months in chronological order using script

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.

Not applicable

Re: Putting months in chronological order using script

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.

Community Browser