topic Re: Putting months in chronological order using script in QlikView

Putting months in chronological order using script

Mon, 27 Jan 2014 09:14:11 GMT

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?

Re: Putting months in chronological order using script

Anonymous — Mon, 27 Jan 2014 09:17:46 GMT

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

Anonymous — Mon, 27 Jan 2014 09:18:56 GMT

Sara

And this one as well :

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

Best Regards, Bill

Re: Putting months in chronological order using script

Mon, 27 Jan 2014 09:25:39 GMT

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.

Re: Putting months in chronological order using script

Mon, 27 Jan 2014 09:30:14 GMT

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.

Re: Putting months in chronological order using script

Mon, 27 Jan 2014 14:04:07 GMT

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.