topic Sort by Month in QlikView

Sort by Month

Mon, 18 Jul 2011 14:59:46 GMT

In my load script, I create a YearMonth field as follows: date(feeddate,'YYYYMM') as YearMonth. I have another field, AsOfYearMonth, that is equal to YearMonth. In my report, I have this calculated dimension - =date(AsOfYearMonth,'MMM'). The report output so far looks like this:

=date(AsOfYearMonth,'MMM')
//=dual(text(date(AsOfYearMonth,'MMM')),
//num(month(AsOfYearMonth)))
//=Month(AsOfYearMonth) 2011 Total Voluntary dual(text(date(AsOfYearMonth,'MMM')),
num(month(AsOfYearMonth)))
Feb 7.4% 2
Mar 15.4% 3
May 11.6% 5
Sep - 9
Apr - 4
Oct - 10
May - 5
Mar - 3
Jan 14.3% 1
Dec - 12
Feb - 2
Apr 14.1% 4
Jun - 6
Jul - 7
Jun 11.1% 6
Nov - 11
Aug - 8
Jan - 1


My problem is that some months (Jan, Feb, Mar, Apr, May, Jun) are repeating, instead of consolidating onto one line. I suspect that this is due to the actual value behind each month.  How can I force these repeating months onto the same line?

Sort by Month

erichshiino — Mon, 18 Jul 2011 15:11:30 GMT

Hi,

Try to create a field in the script with the syntax:

month(feeddate) as Month,

or date(month(feeddate),'MMM') as Month

In your case, I'd say your are right, you have a value with month and year behind the dimension, even if you format it in a different way.

Hope this helps,

Erich

Sort by Month

Mon, 18 Jul 2011 15:52:48 GMT

Create a Field Month(Date) as MonthName in your Date Dimension and use that.

Sort by Month

Mon, 18 Jul 2011 15:55:17 GMT

Thank you. In both cases, this should be done in the load script, not in the report itself, correct?

Sort by Month

erichshiino — Mon, 18 Jul 2011 16:04:33 GMT

Yes, that you go in the script.

You can also include the month as an integer if you still have problems to sort it

num(month(date)) as nMonth

Hope it helps,

Erich

Sort by Month

Tue, 19 Jul 2011 18:15:33 GMT

Thank you both. That worked.