topic Order by monthyear in QlikView

Order by monthyear

fkeuroglian — Fri, 20 Nov 2015 15:30:15 GMT

Hi experts

i have a table only with month-year and i have to order,

Table:

LOAD %MesAño,

Value()

Resident FechasTotales

Order by %MesAño;

if i order by %MesAño, the result is as the image show

order

01-2012

01-2013

01-2014

...

and not in date order

i want to order

01-2012

02-2012

03-2012

what kind of order i have to use?

thank you a lot

Fernando

Re: Order by monthyear

Mark_Little — Fri, 20 Nov 2015 15:34:36 GMT

Hi,

I would make another field to order by.

Something like

SUBDFEILD(DATE,'-',2)&SUBDFEILD(DATE,'-',1) as DateOrder

201201

201202

201301

201302

So 201202 would be less than 201301 so gives you a number to order by.

Mark

Re: Order by monthyear

roger_stone — Fri, 20 Nov 2015 15:35:05 GMT

Sort - by Expression

RIGHT(%MesAño, 4) & LEFT(%MesAño, 2)

Re: Order by monthyear

Fri, 20 Nov 2015 15:35:13 GMT

Hi, Convert Month-Year into Date value and use the order by function or you date field for your order.

Date(Date#( %MesAño,'MM-YYYY')) AS MonthYrDate // It creates date of 1st day of each Month-Year

Re: Order by monthyear

Fri, 20 Nov 2015 16:03:57 GMT

Hi Fernando,

Iam new to the Qlikview. In SQL, we can do like below. I think we do like below.

LOAD %MesAño,

Value()

Resident FechasTotales

Order by replace(%MesAño,'-','');

Thanks,
Sreeman.