Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

ateafilar
New Contributor III

Sort order with year-month

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Sort order with year-month

Right, my mistake.


try:

Expression: MyYear*100+MyWeek

13 Replies
MVP
MVP

Re: Sort order with year-month

Try expression sort :

Expression: MyYear+MyWeek

Not applicable

Re: Sort order with year-month

Chart Properties -> Sort

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

tht will solve ur problm..

ateafilar
New Contributor III

Re: Sort order with year-month

Your suggestion only sorts weeks, heres the result:

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

ateafilar
New Contributor III

Re: Sort order with year-month

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..

MVP
MVP

Re: Sort order with year-month

Right, my mistake.


try:

Expression: MyYear*100+MyWeek

ateafilar
New Contributor III

Re: Sort order with year-month

qv.JPG.jpg

abeyphilip
Contributor II

Re: Sort order with year-month

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
Contributor III

Re: Sort order with year-month

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

Not applicable

Re: Sort order with year-month

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

Community Browser