topic Re: Year Month sort in QlikView

Year Month sort

andrefpc — Fri, 14 Feb 2014 12:27:22 GMT

Hello community,

In a chart I have a calculated date dimension to get the year and the month, like this:

year & '-' & month

but I'm unable to sort this dates, any ideas?

thanks in advance!

alexandros17 — Fri, 14 Feb 2014 12:31:27 GMT

use year * 100 + month, in this way you will have always numbers of 6 digits ex. 201401, 201402 ...

Sorting in this way is natural

Hope it helps

andrefpc — Fri, 14 Feb 2014 12:35:26 GMT

Great! Worked like a charm, thanks Alessandro.

sivarajs — Fri, 14 Feb 2014 12:41:47 GMT

Try this in your sort expression

left(yearmonth,4)&Right(yearmonth,2)

nhlakadee84 — Tue, 09 Apr 2019 12:37:33 GMT

Thank you!

This was quite helpful for me.

martinpohl — Tue, 09 Apr 2019 13:10:53 GMT

Hi,

month is a dual field. With a dual field you can show the string and sort by number.

If you are using monthname(Date) you will get Apr. 2019 for today, depending on your monthname settings and can sort by the numeric values.

If you want to show 2019 - 04 or 04 - 2019 you can load

dual(num(month(Date),'00' & '- ' & year (Date),year(Date)&num(month(Date),'00')) as Monthname

Show your field monthname and sort it by number if not by default.

Regards