topic Re: Sort Order in expression in QlikView

Sort Order in expression

sohb2015 — Mon, 29 Feb 2016 04:23:58 GMT

Hello,

I have a calculated dimension which is used in bar chart. The dimension is the combination of Month and Year fields.

I want to sort the calculated dimension according to the calendar. i.e. Jan 15, Feb 15, Mar 15....Jan 16, Feb 16.

What expression should I write while trying to sort the column.

Thanks

sunny_talwar — Mon, 29 Feb 2016 18:56:34 GMT

Are you combining the Month and Year on the front end or back end?

veidlburkhard — Mon, 29 Feb 2016 19:28:51 GMT

Hi Sohini,

for sorting Month and Year you can use this expression: YY² + Num(Month)

As long as YY is greater or equal than 12 this works fine ascending or descending.

Hope this helps

Burkhard

johnw — Mon, 29 Feb 2016 19:38:17 GMT

I'd make a Month Year dimension defined as a date with a MMM YY format, and just sort numerically.

date(makedate(Year,Month),'MMM YY') as [Month Year]

If you really want to do it as a calculated dimension, again, just make it a date with a MMM YY format, and sort numerically.

=date(makedate(Year,Month),'MMM YY')

I try to avoid both calculated dimensions and sort expressions where practical.

Tue, 01 Mar 2016 02:49:12 GMT

Instead of adding date sort expression, simply create the Calendar table from ascending values and select Load order.

Otherwise add the Month No like 1,2,3 on the Master Calendar and use the Month No in your sort expression.

vikasmahajan — Tue, 01 Mar 2016 05:18:09 GMT

Add Calendar and sort on MonthNumber