Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

oscarmqz
Contributor III

Date sort order in line chart

Hi all, this is driving me crazy Smiley Frustrated 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.

Capture.JPG.jpg

Tags (3)
24 Replies
ecolomer
Honored Contributor II

Re: Date sort order in line chart

And ther order is TEXT type or NUMERIC type??

Will be TEXT

oscarmqz
Contributor III

Re: Date sort order in line chart

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

ecolomer
Honored Contributor II

Re: Date sort order in line chart

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

oscarmqz
Contributor III

Re: Date sort order in line chart

Same result

Re: Date sort order in line chart

Try

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


as Dimension without special sorting expression.

jaimeaguilar
Valued Contributor II

Re: Date sort order in line chart

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

jaimeaguilar
Valued Contributor II

Re: Date sort order in line chart

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

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

Highlighted
oscarmqz
Contributor III

Re: Date sort order in line chart

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

Community Browser