topic Re: Sort monthnames on monthnumber in QlikView

Sort monthnames on monthnumber

paulberendts — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Sort monthnames on monthnumber

satheshreddy — Mon, 23 Oct 2017 10:10:51 GMT

Hi Paul,

use monthname() you will get the monthname.

Regards

Sathesh

Re: Sort monthnames on monthnumber

tresB — Mon, 23 Oct 2017 10:18:33 GMT

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

Re: Sort monthnames on monthnumber

paulberendts — Mon, 23 Oct 2017 10:31:09 GMT

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?

Re: Sort monthnames on monthnumber

Peter_Cammaert — Mon, 23 Oct 2017 10:36:30 GMT

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.

Re: Sort monthnames on monthnumber

tresB — Mon, 23 Oct 2017 10:37:09 GMT

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).

Re: Sort monthnames on monthnumber

Anonymous — Mon, 23 Oct 2017 10:54:16 GMT

Hi,

You can use

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

Regards,

Sahadev

Re: Sort monthnames on monthnumber

paulberendts — Mon, 23 Oct 2017 10:55:23 GMT

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?

Re: Sort monthnames on monthnumber

Peter_Cammaert — Mon, 23 Oct 2017 10:58:56 GMT

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

Re: Sort monthnames on monthnumber

paulberendts — Mon, 23 Oct 2017 11:15:46 GMT

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

Re: Sort monthnames on monthnumber

tresB — Mon, 23 Oct 2017 11:19:13 GMT

Check Uploading a Sample

Re: Sort monthnames on monthnumber

paulberendts — Mon, 23 Oct 2017 11:30:22 GMT

Thanks,

Here is a qvw test file.

What i'm looking for is:

-A column for each month of the year (evenif there are no input records for thes month)

-The columns sorted in the correct sequence (jan-feb-mar etc.)

Thanks,

Paul Berendts

Re: Sort monthnames on monthnumber

tresB — Mon, 23 Oct 2017 11:41:53 GMT

Probably you have forgotten to attach the file. However, now that you say:

Paul Berendts wrote:

..-A column for each month of the year (evenif there are no input records for thes month)

I guess you have to use a master calendar to generate all the missing dates/months. Have a look here:

Master Calendar Generation Script‌

Generating Missing Data In QlikView

Re: Sort monthnames on monthnumber

paulberendts — Mon, 23 Oct 2017 11:42:10 GMT

Thanks,

Here is a qvw test file.

What i'm looking for is:

-A column for each month of the year (evenif there are no input records for thes month)

-The columns sorted in the correct sequence (jan-feb-mar etc.)

Thanks,

Paul Berendts

Re: Sort monthnames on monthnumber

tresB — Mon, 23 Oct 2017 11:50:37 GMT

Try calculated dimension like:

=Month(MakeDate(1,Maand))

Re: Sort monthnames on monthnumber

paulberendts — Mon, 23 Oct 2017 12:49:08 GMT

Thank you, exactly what I was looking for.