topic Order Stacked chart by date field. in QlikView

Order Stacked chart by date field.

Thu, 21 May 2009 22:20:08 GMT

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)

Order Stacked chart by date field.

Anonymous — Thu, 21 May 2009 22:44:29 GMT

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?

Order Stacked chart by date field.

Tue, 26 May 2009 16:49:14 GMT

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

Order Stacked chart by date field.

johnw — Wed, 27 May 2009 01:49:14 GMT

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.

Order Stacked chart by date field.

Anonymous — Wed, 27 May 2009 02:29:29 GMT

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.

Order Stacked chart by date field.

Wed, 27 May 2009 15:57:41 GMT

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.

Order Stacked chart by date field.

Anonymous — Wed, 27 May 2009 20:04:24 GMT

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.

Order Stacked chart by date field.

rwunderlich — Thu, 28 May 2009 11:35:07 GMT


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

Order Stacked chart by date field.

Anonymous — Thu, 28 May 2009 20:32:54 GMT

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.

Order Stacked chart by date field.

rwunderlich — Fri, 29 May 2009 00:44:08 GMT

Michael,

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

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

-Rob

Order Stacked chart by date field.

Anonymous — Fri, 29 May 2009 03:31:47 GMT

I just posted a Wiki "How to create a Calendar" - http://community.qlik.com/wikis/qlikview-wiki/how-to-create-a-calendar.aspx
Maybe it will reduce the amount of questions on that topic (wishful thinking...)

Order Stacked chart by date field.

Fri, 29 May 2009 22:56:15 GMT

OK,

So the way I got round it, was to load the data in order using ORDER BY in the sql statement.

Order Stacked chart by date field.

johnw — Sat, 30 May 2009 00:30:13 GMT

That works, but isn't "The QlikView Way". It seems to me that either we don't understand your problem, or you don't understand our solutions.

QlikView dates are stored internally as the number of days since 12/30/1899. But QlikView gives you lots of options for how you display the dates, including "May 2009". If you use QlikView dates rather than some other date format, you can then use all of QlikView's date manipulation functions, everything shows up ordered correctly, and life is generally easier. You should use QlikView dates.

Here's an example of turning numeric dates into QlikView dates and months. The first format string tells it the format of the input number, the second tells it the format you want to see in QlikView by default (can be overridden on charts). And attached is an application using this exact script, then doing a stacked bar chart by month with the months in order. So far as I understand, that's what you want. It does not require sorting the input data. It only requires using the right format for your dates.

LOAD
date(date#(Date,'YYYYMMDD'),'DD/MM/YY') as Date
,date(monthstart(date#(Date,'YYYYMMDD')),'MMMM YYYY') as Month
,pow(10,rand()*3) as Sales
,pick(ceil(rand()*2),'Company A','Company B') as Customer
INLINE [
Date
20081201
20090522
20090505
20090413
20090108
20090220
20081215
20090221
20090317
20090316
];