topic Sort last months (including last year months) in QlikView

Sort last months (including last year months)

Mon, 04 Apr 2011 09:46:11 GMT

Hi,

I have a line chart showing last 4 months data. (Dec'10 - Jan'11 - Feb'11 - Mar'11), but month dimension shows months sorted by its numeric value (Jan - Feb - Mar - Dec)

How could I sort these months without year information)?

Sort last months (including last year months)

Mon, 04 Apr 2011 09:48:52 GMT

Have you considered/tried sorting by Year-Month value?

Sort last months (including last year months)

Mon, 04 Apr 2011 09:49:47 GMT

Sorry, incomplete previous post. Sorting by Year-Month value as sort by expression in sort tab.

Re. :Sort last months (including last year months)

Mon, 04 Apr 2011 09:51:41 GMT

hi,

one solution is to include an "ORDER BY [Month column]" in your "Load" and use the sort "Load order" for this dimension in your report.

kindly,

Sort last months (including last year months)

Mon, 04 Apr 2011 09:51:52 GMT

I have found a possible solution:

On Sort tab, select Month dimension (called Mnt) and check Expression with the following:

if(Mnt>Month(Today()),-1*(Month(Today())-Mnt),(Month(Today())-Mnt))

Sort last months (including last year months)

Mon, 04 Apr 2011 09:54:33 GMT

Also, for rolling N-months analytical objects, you can create an extra line in your calendar, such as:

autonumber(date(monthstart(Date), 'YYYYMM'), 99) as YMNum.

Then on set analysis side, you set the expression for your N-rolling month to, e.g.:

sum({$<YMNum={$(=max(YMNum))}, Year=, Month=, Day=>} Sales) and sum({$<YMNum={$(=max(YMNum)-1)}, Year=, Month=, Day=>} Sales),

sum({$<YMNum={$(=max(YMNum)-2)}, Year=, Month=, Day=>} Sales), etc.

Then you can set your dynamic label as you wish by copying the related set analysis statement and use in with the only() function, e.g. for the first expression above the dynamic label is: only({$<YMNum={$(=max(YMNum))}, Year=, Month=, Day=>} Month).

Hope that's what you're looking for.

Sort last months (including last year months)

Mon, 04 Apr 2011 09:57:30 GMT

That's very useful! Thank you!