Sorting list of Months

[#1]

Hi,

I have a Field named 'Mes' witch values are 01-Jan, 02-Fev, 03-Mar... and so on.

I've made a List with the expression

Right(Mes, 3),

so I have the right names: Jan, Fev, Mar...

The problem is, I cannot sort them in the original Order. I've tried sorting by Expression, using

num(Left(Mes,2))

but didn't work.

Any Ideas of how to solve this ?

Thanks !!

[pat_agen]

Hi Josué,

just sort them using the original field "Mes".

Make sure on the "Sort" tab that your new field that you created with Right(Mes,3) is at the top of the list., where it says "Priority" Then tick the Expressions box and put the filed name Mes in there. Qlikview will see this field as being alphanumeric so it will sort correctly.

[#3]

PatAgen,

I didn't understand you idea. What do you mean with "is at the top of the list" ? Do you mean to choose Expression in the Sort Tab, and use the field "Mes" in it ? Because that didn't work either.

Dorry for my bad English.

Thanks !!

[pat_agen]

Hi Josué,

your English is fine. It was my brain that was slow. I thought you were talking about a chart and not a list.

i have attached a qvw showing you how to achieve what you wish for. I have done this in the script by creating a small table (it will have 12 records, 1 for each month). It uses the functions you used Right(mes,3) and left(Mes,2) but links them together and to your initial data load. Now you can adress the mes field as it is originally or by the character display or the numeric display.

If you cannot open the file here is the script code:

testData:
load * inline
[
mes, sales
12-Dec,11
11-Nov,56
01-Jan, 10
02-Feb, 20
03-Mar, 30
04-Apr,5
10-Oct,1
05-May,15
06-Jun,25
07-Jul, 3
08-Aug, 7
09-Sep,105
];

sortMes:
load
mes,
right(mes,3) as displayMes,
left(mes,2) as sortMes
resident testData;

hope this is more helpful

[hector · Accepted solution]

Hi,

Another way to do this is using the dual function.

Ex. If you have 01-Jan, 02-Fev, 03-Mar, then you can use this

Subfield(Mes,'-',1) this returns 01, 02, 03....
Subfield(Mes,'-',2) this returns Jan, Fev, Mar....

then mixing up with the dual function, you can do this

dual(Subfield(Mes,'-',2),Subfield(Mes,'-',1)) as Mes_Num

Then this new field Mes_Num will have the text Jan,.Fev, Mar BUT internally the value will be 1,2,3, so you can sort it by numeric value

Hope it helps

Rgds

[#6]

I've made a List using

dual(Subfield(Mes,'-',2),Subfield(Mes,'-',1))

as Field and used Numeric Value in Sort Tab, and worked fine !!!

Thank you, PatAgen and Hector, for all your help !!!