topic Sorting Months (Jan-Dec) having data from many years. in QlikView

Sorting Months (Jan-Dec) having data from many years.

montubhardwaj — Tue, 20 Jul 2010 17:01:48 GMT

Hi All,

I am little struck up with problem. Need your help. Here goes the description:

I have a filed "Months " which is having three years of data i.e like Jan-09,Aug-09,Mar-09,Dec-10,May-10,Mar-11,Jun-11 etc. I am using this field in the Dimension of Charts. It is working all fine but the problem is with sort. Ideally, it should show like Jan-09 through Dec-09 and then Jan-10 through Dec-10 . But now it is sorting in some random fashion. I have shown the problem below. Need to get it sorted properly.Any suggestions?

Sorting Months (Jan-Dec) having data from many years.

Tue, 20 Jul 2010 17:53:53 GMT

You can create a field like this:

dual(Month(cNumDate) & '-' & Year(cNumDate), num(Year(cNumDate) & num(Month(cNumDate), '00'))) as cYearMonth

After this you can sort this field as Numeric Value. See attachment.

Sorting Months (Jan-Dec) having data from many years.

hector — Tue, 20 Jul 2010 19:31:51 GMT

Hi, i prefer this way

dual(Month(cNumDate) & '-' & Year(cNumDate), Year(cNumDate)*100 + Month(cNumDate)) as cYearMonth

then sort by numeric value

Rgds

Sorting Months (Jan-Dec) having data from many years.

montubhardwaj — Wed, 21 Jul 2010 09:00:36 GMT

Thank You very much Guys!!

Your solution worked perfectly.... Kudos...!!!