topic Re: sorting dimension on date in QlikView

sorting dimension on date

chriscools — Mon, 17 Dec 2012 15:12:26 GMT

Hello,

i have a calculated dimension like this:

month ( orderdeliverydatefrom) &"-"& year (orderdeliverydatefrom).

it works but the sorting is wrong:

mar-2013

jan-2013

jul-2012

feb-2013

and so on.

i tried working with sorting by an expression on the calculated dimension.

and there i sorted ascending with the field "orderdeliverydatefrom" in the expression field.

but it doesn't work.

can someone help me with this?

thanx!

chris

Re: sorting dimension on date

Miguel_Angel_Baeyens — Mon, 17 Dec 2012 15:25:05 GMT

Hi Chris,

The best way to get this done is to create an actual "SortDate" field in the script, then use it in the Sort tab of the chart properties. For QlikView, "jan-2013" is a text string, and it will sort by ASCII codes, not as dates. If you use a date field that QlikView understands as numeric (using function Date() for example) the the sorting will make sense.

Hope that helps.

Miguel

Re: sorting dimension on date

CELAMBARASAN — Mon, 17 Dec 2012 15:27:25 GMT

Change your field creation with the use of dual

Dual( month ( orderdeliverydatefrom) &"-"& year (orderdeliverydatefrom) ,Num(Year(orderdeliverydatefrom) & Num(month ( orderdeliverydatefrom))))

And use Sort by exp as numeric

Re: sorting dimension on date

Mon, 17 Dec 2012 15:34:50 GMT

Or just use

Max(Year(DatePurchase) & Num(Month(DatePurchase)))

As sort-by expression