Sort monthnames on monthnumber

[paulberendts]

I have a SQL table with three integer columns, Year, month and total. I can show that in a 'Draaitabel' with month horizontal and year vertical. Horizontal needs to be sorted on month (1,2,3). Now I want not to show the month numbers, but the month names. I can do that  with the Longmonthnames, but then the sorting goes all wrong. Houw can I sort the monthnames on their 'integer' values?

[satheshreddy]

Hi Paul,

use monthname() you will get the monthname.

Regards

Sathesh

[tresesco]

Create month field in the script using month(), like:

Load

          Date(Makedate(Year,Month, <Day>)) as Date,

          Month(MakeDate(Year,Month, <Day>)) as Month

If you don't have Day field, you can leave the third parameter blank like: makedate(Year,Month). Now you get short month names that sort properly. It's always better to create date fields using proper qlik date functions to avoid unwanted complexity/behaviour in the UI. If you want to sort across years, then create monthname like:

          MonthName(MakeDate(Year,Month, <Day>)) as MonthNames

[paulberendts]

thanks for the help.

I was already able to create the name of the month,

I want to show only the name of the month and not the monthnumber, but it needs te be sorted on the monthnumber.

Can you help me with the sorting?

[Peter_Cammaert]

Tres was explaining to you how to create Months as dual values. A Dual value carries both a textual and a numerical representation of the Month. By default, a field that is used as a dimension will show the textual representation (with proper alignment) and sort according to the numerical representation. If this doesn't work, goto the Sort tab and select 'Numerical' to sort your month names.

If this doesn't work, you still don't have Months as dual values.

[tresesco]

If you create Month field using Month() as I showed above, you get a dual field (number at the backend and string at the front), i.e. You will see Jan,Feb,... in the UI it would sort properly. Try it. If you don't want to go back to the script, let know how does your month field look like and what is your expected output (may be a qvw or screen shot).

[Anonymous]

Hi,

    You can use

         Dual(Month,MonthNumber) function to sort MONTH by MonthNumber easily

Regards,

Sahadev

[paulberendts]

I read the data from an SQL database.

Dat date is derived from a fielr Regieritnr, which has the form YYMMDDxxxxx

I generate year and month  as

ODBC CONNECT TO DWHSQL;

SQL SELECT

('20' + left(ltrim(STR(Regieritnr)),2) as Jaar,

right(left(ltrim(STR(Regieritnr)),4),2) as Maand,

Reiseenheden_gemeente as Aantal

FROM RegieRit

The function MakeDate does not exist for SQL server.

What I can use is this:

   Month(MakeDate('20' + left(ltrim(STR(Regieritnr)),2),right(left(ltrim(STR(Regieritnr)),4),2))) as Month,

But Month only shows as an integer.

This shows as a string:

DATENAME(month,'20' + left(ltrim(STR(Regieritnr)),2)  + '-' + right(left(ltrim(STR(Regieritnr)),4),2) + '-01') as MaandString,

but it cannot sort as a number.

What an I doing wrong?

[Peter_Cammaert]

What happens if you use a couple of function calls directly on the DB column? Like:

:

Month(Date#(left(Regieritnr, 6), 'YYMMDD')) AS Month,

:

Best,

Peter

[paulberendts]

I would like to send a qvw, but I don't know how to do that.