Unable to sort chart by Date field (or any other other field except TEXT type)

[imtiaz_ullah]

Hi all,

I am test-driving QlikView PE and am having a really odd issue for something so basic.

I am trying to sort a chart on date and as I am returning month name in my axis I am sorting on EXPRESSION which is my DateTime field coming back from SQL Server as a DATETIME.

The chart refuses to sort by this or any other 'sort by' option, other than TEXT, at which point it sorts A-Z and Z-A without issue.

Am I missing the point here somewhere?

Thanks Immy

[stigchel]

Hi Immy,

If your field is indeed a datetime field as you suggest you can simply sort by numeric value. A date is a so called Dual field type with always a number and a string representation.

Regards,

Piet Hein

[imtiaz_ullah]

Hi Piet,

I had already tried doing this too but it won't work

I don't understand why it refuses to accept any sort other than text.

Thanks

Immy

[stigchel]

Can you post your QlikView file, use the advanced editor to attach a document

[#5]

Hi,

It looks like your month value comes from SQL Server.

You're sorting on MessageDateId but you probably have several years in your data.
If the first record is 01/10/2000 and the second one is 01/01/2015, it leads to display October before January.

You should create a new field and sort on that field on QlikView like :
Month(MyDate) as QVMonth

[preminqlik]

first qlikview needs to recognize the value of your date field so that it internally stores date as in number format...

for example in your above example

use this for month sorting with expression , and make it as ascending.

=only({1}num(Month(Date#(Capitalize(left(trim(Month),3)),'MMM'))))

what is your date format :...i mean give example value of date field

[imtiaz_ullah]

Good morning guys, thanks for all your input - i've managed to get it working another way (see attachment), however I am now challenged in the same way via QlikSense (is this the right forum to continue this issue?). See attachment 2.

It refuses to sort on anything other than Alphabetically.

None of my tricks will work on QlikSense, i just can't believe the product is this temperamental?

Thanks

Immy

[#8]

It's working fine in Qlik Sense as well for me.

Data:

Load * Inline [

MyDate, MyMonthName, Qty

21/10/1978, October, 10

13/09/1982, September, 20

08/01/1954, January, 30

01/04/2010, April, 5

20/01/2015, January, 8];

Graph with MyMonthName as dimension, Sum(Qty) as measure, and sorted on Month(MyDate)

[stigchel]

For QlikView, this works because the Function Month() is an function resulting in an integer and so you can sort on numeric value. The function MonthName() is a function with a dual type as result, which will sort fine on numeric value. Your original post suggests that you use Month as Dimension, which because of the problems, is probably only a text field. In your original post you try to sort on MesageDateID, which is probably the DateTime field from your sql. Problem with this is that it has several possible values for each month text, so qv can't resolve the sorting!

My advice is to make a desired Dimension Field in script in (dual) date format, you can format the text as you like e.g.

Date(MessageDateID,'MMM-YY') as MyDateDimension

Use the MyDateDimension as Dimension in your chart and the sorting on numeric value should be fine

[krishna20]

Hi Imtiaz,

Have you tried by selecting Y-Value as ASEC or DESC in the Sort Tab..?if not try it.

Regards

Krishna