Order by monthyear

[fkeuroglian]

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

[Mark_Little · Accepted solution]

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

[roger_stone]

Sort - by Expression

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

[#4]

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

[#5]

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.