topic Sorting months considering current month as the first month in QlikView

Sorting months considering current month as the first month

Mon, 16 Sep 2013 11:48:22 GMT

Hi,

I want to have a chart that displays the information sorted in months i.e., i want current month-1 to appear as the first month,current month-2 to appear as the second month and so on upto current month-12 appearing as the last month in the chart.Can anyone help me accomplish this please

Re: Sorting months considering current month as the first month

tresB — Mon, 16 Sep 2013 11:59:07 GMT

month-1, month-2... are your months right? if so, try:

=Dual(MonthField, Num(SubField(MonthField,'-',2)) )

Or,

simply: = Num(SubField(MonthField,'-',2))

This should be your custom sort expression.

Re: Sorting months considering current month as the first month

Mon, 16 Sep 2013 12:08:39 GMT

I did try the above two approaches by placing the expression given in the expression field of sort tab in the chart but its not working

Re: Sorting months considering current month as the first month

Gysbert_Wassenaar — Mon, 16 Sep 2013 12:09:01 GMT

If your month field is numeric (or dual value like when created with the Month() function) you can use rank(month) as expression for sorting. If it's a string value you'll have to create a numeric month in the script first. If you have a date field then use the monthstart function to create such a field: date(monthstart(MyDate),'MMM') as MyMonth

Re: Sorting months considering current month as the first month

tresB — Mon, 16 Sep 2013 12:26:53 GMT

Data format dependency is there. Please upload a sample with small data.

Re: Sorting months considering current month as the first month

Tue, 17 Sep 2013 04:10:39 GMT

rank(month) sorts the month as Jan,feb,Mar... dec but i need to sort it as aug(current month-1),jul(current month-2),jun(current month-3) and so on upto september which is (current month-12). I have acheived the same by using the following expression in the sort tab:

match(MyMonth,Month(AddMonths(Date(Today()),-1))
,Month(AddMonths(Date(Today()),-2))
,Month(AddMonths(Date(Today()),-3)),Month(AddMonths(Date(Today()),-4)),
Month(AddMonths(Date(Today()),-5)),Month(AddMonths(Date(Today()),-6)),Month(AddMonths(Date(Today()),-7)),Month(AddMonths(Date(Today()),-8)),Month(AddMonths(Date(Today()),-9))
,Month(AddMonths(Date(Today()),-10)),Month(AddMonths(Date(Today()),-11)),Month(AddMonths(Date(Today()),-12)))

But then it would be helpful if anyone could give an altenative method to acheive this as the method that i have been using seems to be more of hard coding

Re: Sorting months considering current month as the first month

Tue, 17 Sep 2013 04:34:00 GMT

Hi,

PFA containing the application

I have achieved the sorting capability using the expression:

=match(MyMonth,Month(AddMonths(Date(Today()),-1))

,Month(AddMonths(Date(Today()),-2))

,Month(AddMonths(Date(Today()),-3)),Month(AddMonths(Date(Today()),-4)),

Month(AddMonths(Date(Today()),-5)),Month(AddMonths(Date(Today()),-6)),Month(AddMonths(Date(Today()),-7)),Month(AddMonths(Date(Today()),-8)),Month(AddMonths(Date(Today()),-9))

,Month(AddMonths(Date(Today()),-10)),Month(AddMonths(Date(Today()),-11)),Month(AddMonths(Date(Today()),-12)))

But,can you please suggest an alternative method for the same as the one that I have been using seems more like hard coding

Thanks & Regards

Anushree Shetty,

+91 9535028666

Re: Re: Sorting months considering current month as the first month

tresB — Tue, 17 Sep 2013 04:50:02 GMT

Hi Anushree,

There would be many other ways, this one just clicked now. Used expression:

=If(Month(MyMonth)-Month(Today())<0, (Month(MyMonth)-Month(Today())+12),(Month(MyMonth)-Month(Today())))

PFA.