Order Stacked chart by date field.

[#1]

Hi, I've tried to Sort Stacked chart by date field. Using "numeric Ascending"

Date field is displayed as "May 2009".."Apr 2009"... etc..

However it does not change it's order.

I would like to keep the display as "MMM YYYY" but still have it sorted.

Any help?

Tried:

=

date(a, 'MMM YYYY')

and also:

=

month(a) & ' ' & Year(a)

[Anonymous]

This shouldn't be a problem. Make sure that your field "a" is numeric (date is also numeric). Can you post an example of the problem?

[#3]

Ok so lets look at the SQL Statement I made:

SQL SELECT convert(date, Date, 103) as MyDate,
ID as MyID,
Value as MyValue
FROM MyDatabase
WHERE Date>= '20080101';

First of all I'm not convinced that the WHERE clause is correct, since I havn't told it should compare Date to a date in the format YYYYMMDD.

So if you could advise me there I would appreciate it.

Otherwise that's how I pull the data in, and the type IS (or rather should be) a date, which is as you say numeric, yet it still doesn't order them correctly.

Any help?

Thanks

[johnw]

Well, I don't know what format you're using in your database for Date. In our shop, it's usually a numeric field in the form YYYYMMDD. For the sake of argument, I'll assume your shop is the same. Then I'd do it more like this:

LOAD

date(date#(Date,'YYYYMMDD'),'MM/DD/YY') as MyDate

,ID as MyID

,Value as MyValue

;

SQL SELECT

Date

,ID

,Value

FROM MyDatabase

WHERE Date >= 20080101

;

The main idea, though, is that you want MyDate to be a QlikView date, which has a numeric internal representation, which means you can sort by it. I could be way off base on where your problem lies, though.

[Anonymous]

It's easy to see the format of your "MyDate" field in the Document properties - number.
And you can convert it in the the script using many ways, including that from John.

[#6]

Well the format I'm using in my database shouldn't matter since I'm using convert() to pull the date into the format I expect - 103 which is dd/mm/yy

But it is stored as a Date.

I presume when you say numeric, you mean an integer.. the old way of storing..

See I can get an ordering, but only if I set my X axis to display YYYY MM, because YYYYMM format will by design always be in order as an integer.

But this is a pathetic approach, and I would expect qlikview to distinguish between and keep separate the model or internal representation of a date to the view or display of the date.

I should be able to request the Days of the month to be displayed in terms of different breed of monkeys. So long as I define an ordering, qlikview should look at the internal representation of a date, order by that, and then convert it into a monkey...

Are you seriously telling me qlikview orders by what is displayed on the X axis and not by what is held internally?

=Date(HistoryScoreDateB, 'YYYY') & ' '&Date(HistoryScoreDateB, 'MM')

Is the only expression that works and it's all smoke and mirrors since

=Date(HistoryScoreDateB, 'MM') & ' '&Date(HistoryScoreDateB, 'YYYY')

gives me a numeric ordering, but not a date ordering.

[Anonymous]

You don't understand. First, the date is numeric but not integer. Second, even presentation like 'May-09' can be numeric in QV world... And, I recommended to check the format not in the database, but after you loaded it in QV.
See attched calendar example, and notice that fields Date, Month, Weekday, MonthYear - all sorted by numeric order, and can be sorted this way in a chart as well.

[rwunderlich]

Alkaline wrote:
=

date(a, 'MMM YYYY')

I'm guessing from your first post that you are defining the dimension as a Calculated Dimension? If so, the date() expression will generate a set of strings which is not what you want. You want dual values. Two options.

1. Change the document format of the field to be MMM YYYY.

2. Create a new field in the script like:

date(thedate, 'MMM YYYY') as YearMonth

and then use the YearMonth as your dimension.

-Rob

[Anonymous]

Rob,
The script you suggest will create 'May 2009' 31 times, because it's just a date in the 'MMM YYYY' format. To get it only once, I always use dual(), like this:

dual(month(thedate) & ' ' & year(thedate), year(thedate) & num(month(thedate), '00')) as MonthYear

Dual is used by QV as a number but is presented as a string. Or as a number as well, if you wish so. No problem with sorting anyway.

[rwunderlich]

Michael,

Good point. In practice what I usually do is use the MonthStart date:

date(MonthStart(thedate), 'MMM YYYY') as MonthYear

-Rob