topic Re: Sort order with year-month in QlikView

Sort order with year-month

Anonymous — Fri, 30 Aug 2013 08:40:35 GMT

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

Re: Sort order with year-month

tresB — Fri, 30 Aug 2013 08:43:30 GMT

Try expression sort :

Expression: MyYear+MyWeek

Re: Sort order with year-month

Fri, 30 Aug 2013 08:46:32 GMT

Chart Properties -> Sort

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

tht will solve ur problm..

Re: Sort order with year-month

Anonymous — Fri, 30 Aug 2013 08:48:37 GMT

Your suggestion only sorts weeks, heres the result:

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

Re: Sort order with year-month

Anonymous — Fri, 30 Aug 2013 08:52:42 GMT

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

Re: Sort order with year-month

tresB — Fri, 30 Aug 2013 08:54:00 GMT

Right, my mistake.

try:

Expression: MyYear*100+MyWeek

Re: Sort order with year-month

Anonymous — Fri, 30 Aug 2013 08:55:35 GMT

Re: Sort order with year-month

abeyphilip — Fri, 30 Aug 2013 08:57:59 GMT

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

Re: Sort order with year-month

michael_maeuser — Fri, 30 Aug 2013 08:59:13 GMT

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

Re: Sort order with year-month

Fri, 30 Aug 2013 09:06:44 GMT

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

Re: Sort order with year-month

Fri, 30 Aug 2013 09:26:25 GMT

Hi Filip,

Do you have to keep the format as YYYY-WW or can it be changed to YYYYWW?

if it can be changed, just use

Year(MyDate)*100+Week(MyDate) as MyYearWeek

instead of your original expression and then use numeric sort on the chart. It will work.

Thanks and BR.

Abhinava

Re: Sort order with year-month

Anonymous — Fri, 30 Aug 2013 09:28:55 GMT

Thanks, works great.

Re: Sort order with year-month

Anonymous — Fri, 30 Aug 2013 09:29:41 GMT

Thank you all for your help.

Re: Sort order with year-month

techvarun — Fri, 30 Aug 2013 09:31:00 GMT

Useful discussion. bookmarked it

Re: Sort order with year-month

vivek_batra — Thu, 13 Oct 2022 06:18:22 GMT

It solved my problem of sorting my X-Axis which is like MonthName-Year. Thanks