Ok so lets look at the SQL Statement I made:
SQL SELECT convert(date, Date, 103) as MyDate,
ID as MyID,
Value as MyValue
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.
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:
date(date#(Date,'YYYYMMDD'),'MM/DD/YY') as MyDate
,ID as MyID
,Value as MyValue
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.
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.
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.
CalendarExample.qvw 123.5 K
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.
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.
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...)
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.
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
testMonthStackedBars.qvw 112.8 K