topic Sorting the dates in x-axis in QlikView

Sorting the dates in x-axis

jagan — Tue, 23 Feb 2010 06:50:59 GMT

I have dates in the following format like

d-4 28-Jan

d-3 29-Jan

d-2 30-Jan

d-1 31-Jan

d0 1-Feb

d1 2-Feb

d2 3-Feb

How to arrange dates in this order in x-axis, i used both the sort by number and text, but the order is changed.

Sorting the dates in x-axis

achiever_ajay — Tue, 23 Feb 2010 07:42:28 GMT

hi,

I think the problem here is you are not following a std format.. wat is de difference between

"d-1 and d1 "

"d-2 and d2"

as your data has differences. sorting is not working for you

Sorting the dates in x-axis

Tue, 23 Feb 2010 09:16:17 GMT

You could use a dynamic dimension in which you conver the text strings to dates.

Sorting the dates in x-axis

johnw — Wed, 24 Feb 2010 01:13:11 GMT

It sounds like your dates are actually text? If so, you could use a dual() function to assign an actual date value to them, something like:

dual('d' & num(Date-AnchorDate,'#,##0') & ' ' & date(Date,'DD-MMM'),Date) as RelativeDate,

You should then be able to sort them numerically.

Sorting the dates in x-axis

Thu, 23 Sep 2010 08:37:40 GMT

Hello...I have a similar problem...

My x-axis is shows a TransMonthYear field (TransMonth & ' ' & TransYear as TransMonthYear ) this is how I want it to be displayed on the graph but is causing havoc with sorting the dates in the correct date order.

Will John's suggestion work for me? If so could someone explain it again for me as I couldn't follow the logic behind the statement 8-)

Many thanks!!! Mike

Sorting the dates in x-axis

kji — Thu, 23 Sep 2010 08:42:08 GMT

If the data arrives in the correct order from your datasource, sort by "Load Order" could work.

Sorting the dates in x-axis

Thu, 23 Sep 2010 08:51:20 GMT

Hi Johan... unfortunatly is doesn't come in such a nice order... Mike

Sorting the dates in x-axis

Thu, 23 Sep 2010 15:20:33 GMT

Does anyone have any suggestions? Been trying all morning...

Sorting the dates in x-axis

johnw — Thu, 23 Sep 2010 17:55:53 GMT

date(makedate(TransYear,TransMonth),'MMM YYYY') as TransMonthYear // or whatever format you need for month and year

Sorting the dates in x-axis

Fri, 24 Sep 2010 14:22:04 GMT

Thanks John... but still no joy... perhaps this might help...

These are the values in my TransYear and TransMonth fields

This is the new line in my load statement...

This loads ok but there when I look in the TransMonthYear field it is empty

Any other QlikTips?

Sorting the dates in x-axis

johnw — Fri, 24 Sep 2010 17:25:53 GMT

Ah, OK. It looks like the issue is that your TransMonth is currently just a text field, where I'd guessed it was a month() field. How about this?

date(date#(TransMonth&TransYear,'MMMYYYY'),'MMM YYYY') as TransMonthYear

Also, you might want to turn your TransMonth into a month() instead of text, which will let it sort properly in your list box and charts:

month(date#(TransMonth&TransYear,'MMMYYYY')) as TransMonth

Sorting the dates in x-axis

Fri, 24 Sep 2010 17:48:44 GMT

Thanks John... perfect timing 5:30 on Friday afternoon and all my graphs now display in the right order...

Job done... Thanks again!!! Mike