topic Re: Date sort order in line chart in QlikView

Date sort order in line chart

Anonymous — Thu, 18 Sep 2014 21:33:25 GMT

Hi all, this is driving me crazy :S I've tried everything that came to my mind, I combining 2 years in 1 chart using just month-day as dimension, but I cannot manage to get it sorted correctly, I'm already ordering by date field in load script and sort property in the dimension, but still, the dates not matching. Those at the end happen to be those days that were reported just in one year but not the other, any idea on how to solve this? Thanks in advance!

Dimension: Month([Report Date]) & '-' & Day([Report Date])

Sort: Expression -> [Report date]

I've also tried to create the dimension as text in load script and sort by load order but I'm getting the same result.

Re: Date sort order in line chart

ecolomer — Thu, 18 Sep 2014 21:49:09 GMT

And ther order is TEXT type or NUMERIC type??

Will be TEXT

Re: Date sort order in line chart

Anonymous — Thu, 18 Sep 2014 21:52:58 GMT

none, if numeric, no changes when change it to text goes from A-Z....

Re: Date sort order in line chart

ecolomer — Thu, 18 Sep 2014 21:57:44 GMT

Then erased all selection to order and select Expresion, and put[ Report Date]

Re: Date sort order in line chart

Anonymous — Thu, 18 Sep 2014 22:17:18 GMT

Same result

Re: Date sort order in line chart

MarcoWedel — Thu, 18 Sep 2014 22:39:55 GMT

Try

Date([Report Date], 'MMM-D')

as Dimension without special sorting expression.

Re: Date sort order in line chart

jaimeaguilar — Thu, 18 Sep 2014 22:50:50 GMT

Hi,

this would be a little tricky to explain, but I'll do my best,

I think the order in your graph is correct. The problem is the new dimension you've created. For example, let's say you pick nov 28. This date is composed by Month = November and day = 28. However, in this new dimension it will concatenate every november that exist in your data model, so included in that Nov 28, there could be included Nov 28 from 2014, from 2013 and so on.

As you correctly pointed out, the last values that appear in your graph only exist in one year but not the other. This make sense, because these are dates that only exist in the most recent year.

So what I'm trying to say is that even if for us is logical that ene - 3 should appear just before ene - 4, for QlikView is different because in the backend is still separating MonthDays from one year from Monthdays from other year.

I'm curious about the correct answer for this, I will keep trying. I hope somebody could enlighten us here.

Btw Numeric Value sort will not work because of what I've explained,

regards

Re: Date sort order in line chart

jaimeaguilar — Thu, 18 Sep 2014 23:23:34 GMT

If you select just one year it would be sorted as you expect, but I think that what you want is to get the months sorted in the "normal order" (ene, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) regardless of the selections, right?

Re: Date sort order in line chart

MK_QSL — Fri, 19 Sep 2014 08:17:23 GMT

Go to Sort Tab

Select your Calculated Dimension

Write Below in Your Expression

=Date#(Month([Report Date]) & '-' & Day([Report Date]),'MMM-D')

Now sort by Numeric Value Ascending/Descending Order

Re: Date sort order in line chart

Anonymous — Fri, 19 Sep 2014 14:06:36 GMT

That's right, this all started because they want to be able to compare prior year values in same chart

Re: Date sort order in line chart

Anonymous — Fri, 19 Sep 2014 14:10:16 GMT

Thanks Manish but it didn't work, it gave me even weirder results

Re: Date sort order in line chart

MK_QSL — Fri, 19 Sep 2014 14:10:56 GMT

Provide sample data.... please

Re: Date sort order in line chart

Anonymous — Fri, 19 Sep 2014 14:15:33 GMT

Here's is the sample file

Re: Date sort order in line chart

MK_QSL — Fri, 19 Sep 2014 14:25:49 GMT

Change in script as below

date([Report Date],'MMM-DD-YYYY') as [Report Date],

and try again..

Re: Re: Date sort order in line chart

ecolomer — Fri, 19 Sep 2014 14:27:02 GMT

can you see the file ...

Re: Date sort order in line chart

Anonymous — Fri, 19 Sep 2014 14:30:01 GMT

Hi Enrique,

It is still not sorted correctly

Re: Date sort order in line chart

Anonymous — Fri, 19 Sep 2014 14:34:23 GMT

The only change is -dd- by -DD-?

I did it but nothing changed....

Re: Re: Date sort order in line chart

ecolomer — Fri, 19 Sep 2014 14:50:47 GMT

if you add the year?

Re: Date sort order in line chart

Anonymous — Fri, 19 Sep 2014 15:16:48 GMT

That's not the approach I want to overlap both years in 1 chart

Re: Date sort order in line chart

travis_craven — Fri, 22 Apr 2016 11:55:50 GMT

I ran in to the same problem. I used Date(MonthStart(DateField), 'MMM-YYYY') to resolve my issue.