topic Re: Sort date using expression in QlikView

Sort date using expression

Fri, 09 Nov 2012 13:29:21 GMT

I have a DATE_FIELD that I use as dimension with QuarterName(DATE_FIELD)

Now I need to sort it like :

"Jan-Mar 2011" "Jan-Mar 2012" "Apr-Jun 2011" "Apr-Jun 2012"

I tried the following sort expression :

if(QuarterName(DATE_FIELD ) = 'Abr-Jun 2012', 1, 2)

Just for testing and nothing happened.

I was expecting the following to work :

Num(Month(DATE_FIELD ))

Since the expression order takes numbers to order values, but it just randomized the sort.

Re: Sort date using expression

Gysbert_Wassenaar — Fri, 09 Nov 2012 13:33:23 GMT

try: Num(Month(DATE_FIELD )) & Year(DATE_FIELD )

Re: Sort date using expression

Fri, 09 Nov 2012 13:38:20 GMT

Didn't work, it orders like this :

"Apr-Jun 2012" "Jan-Mar 2012" "Jul-Sep 2012" "Oct-Dec 2011" "Apr-Jun 2011" "Jan-Mar 2011" "Jul-Sep 2011"

Makes no sense.

Same order as

Num(Month(DATE_FIELD ))

Re: Sort date using expression

Gysbert_Wassenaar — Fri, 09 Nov 2012 13:57:46 GMT

Can't seem to get it to work either by sorting on expression. Only by adding another calculated dimension =num(month(DATE_FIELD),'00') & '-' & year(DATE_FIELD), sort that numerically and hide the column.

Re: Sort date using expression

Fri, 09 Nov 2012 14:08:41 GMT

Thanks for helping

What I did was create another table with Quarter (without the year) and Month values, then I used 2 dimensions, one with my quarter dimensions and another with Year(Date_field), that worked, need 2 dimensions but at least it's showing properly now.