Sort order with year-month

[Anonymous]

Hello everyone,

I have a problem with the sort order in a bar chart. On the x-axis I have a field, let's call it MyYearWeek, which contains the following values:

2013-51

2014-1

2014-12

2014-2

My problem is that I don't know how to sort it correctly, right now with my A->Z sorting the values presents as 2013-51, 2014-1, 2014-12, 2014-2 of course... I really do want to keep the 'YYYY-WW' formatting for my field values. How to sort it within the chart object sort tab, without changing my script below... Perhaps I could use the Year field and Week field seperately for sorting?

My script:

Year(MyDate) As MyYear,

Week(MyDate) As MyWeek,
Year(MyDate) & '-' & Week(MyDate) As MyYearWeek

Thanks in advance.

Regards,

Filip

[tresesco]

Try expression sort :

Expression: MyYear+MyWeek

[#3]

Chart Properties -> Sort

remove all the sorting and then the date field select Number Ascending / Descending

tht will solve ur problm..

[Anonymous]

Your suggestion only sorts weeks, heres the result:

2014-1
2014-2
2014-12
2013-51

[Anonymous]

If you mean sort by 'Numeric Value' Ascending.... then it doesn't work at all.

I only have one dimension and using QV 11 if it helps in any way..

[tresesco · Accepted solution]

Right, my mistake.

try:

Expression: MyYear*100+MyWeek

[Anonymous]

[abeyphilip]

Hi Filip,

Try this in your chat sort expression:

num#(SubField(MyYearWeek,'-',1)&num(SubField(MyYearWeek,'-',2),'00'))

And then sort ascending or descending.

Regards,

Abey

[michael_maeuser]

create a field year(mydate) and week(mydate) as xxx and sort by this field

[#10]

Try loading like this

Year(MyDate) As MyYear,
Week(MyDate) As MyWeek,
Dual(Year(MyDate) & '-' & Week(MyDate), Num(Mydate)) As MyYearWeek

This way you tell Qlikview that MyYearWeek is a date, but the text formatting is YYYY-WW. Sorting will be based on the underlying numeric value