Little problem with ordering

[#1]

Hello everyone,

I'm a fairly new user of QlikView and so far everything went well, but now I have a little problem with ordering data in a graph; here's the description of the problem:

I want to display in a graph the number of storage movements that were made divided by the month of the year. Since I had the date of the movement I created a little inline load to associate the month as a 2digits-number with the short month name (for example: 04 = apr.)

So far so good but the problem arised when the data started to span two or more years, then all the movements made in the same month got added together in the graph regardless of the year.

To solve the problem I added the year in the dimention of the graph (like this: "MonthName&' '&year(Date)"), this clearly divided the movements made in, say, apr 2008 from those made in apr 2009 but then the ordering got screwed up since leaving the "Original Order" in the ordering rules led to something like this:

Jan 2009, Jan 2008, Feb 2009, Feb 2008 and so on.

I'd like to see the months in the correct order divided by year but every ordering rule and expression I came up with ended up in the same result, except A-Z ordering, which ordered everything correctly except that even the month names clearly ended up being ordered alphabetically.

How can I solve this problem? For the moment I added a little year selection label in the sheet but I'd like to see the data in the correct order without having to select a specific year.

Thanks in advance and sorry for my english.

Michele.

[#2]

Hi,

Not sure if it is right for your case, but it may help.

In Script,

load .....,

Month(Date) as Month,

Year(Date) as Year

resident yourtablename;

In graph,

Use both Year and month as Dimension. Promote Year to the top, Month after it.

It should give you what you want.

Ivan

[#3]

Unfortunately the Dimension of that graph is a cyclic group composed as follows:

Date (Full date, ordered correctly)
Week (This one i was able to order correctly because it's a number so numeric ordering works)
Month
Year (ordered correctly)

If I add the year as a new Dimension, it completely screw the graph as the year get fixed on the bottom and the rest of the data get ordered vertically on the right.

[prieper]

You may use the functions WeekName(YourDate), MonthName(YourDate), QuarterName(YourDate). Sorting will work. If you do not like the display, you may check the DUAL-function in the OL-Help.

HTH
Peter

[#5 · Accepted solution]

My preferred way is to treat the MonthYear field you need as a true date. Then it will always sort properly. Use something like:

LOAD date(monthstart(Date),'MMM-YYYY') as MonthYear

The key is to use the monthstart function to "roll-up" all dates to the first date of the month so QlikView sees them as the same date. Otherwise you might see a Jan 2009 with an internal date of 1/1/09 and a second Jan 2009 with an internal date of 1/2/09 and so on. Of course you could use the end of the month or any other date, as long as you are consistent.

Tom

[#6]

Your solution worked like a charm, thank you Tom.

Michele.