topic Re: Sorting Date in QlikView

Sorting Date

Mon, 01 Sep 2014 09:09:06 GMT

I have a column with unsorted Dates in the format 'DD.MM.YYYY hh:mm:ss'. I would like to extract Date from this data in the format 'MM.YYYY' and also in the sorted order. I'm using Date(MonthStart(DAT), 'MM.YYYY'), where DAT is the name of my column. I'm getting the date in 'MM.YYYY' format but I'm not able to get it in sorted form. I'm using this extracted format in a chart and I tried to sort by setting Expression 'DAT' as ascending in the sort tab of chart. But no success so far.

Please help me in achieving this. Thanks in advance..!!!

Re: Sorting Date

MK_QSL — Mon, 01 Sep 2014 09:23:12 GMT

Use below code in your script.. .Change necessary field names...

DUAL(Date(Floor(TimeStamp#(DATE,'DD.MM.YYYY hh:mm:ss')),'MM.YYYY'),Date(Floor(TimeStamp#(DATE,'DD.MM.YYYY hh:mm:ss')),'YYYYMM')) as RequiredDate

Re: Sorting Date

alfredlee — Mon, 01 Sep 2014 09:32:37 GMT

Can you try to sort by the expression Year(DAT) * 100 + Month(DAT)?

In case the DAT is not a real date field (i.e. Text), you can use Mid to extract the corresponding section (e.g. MID(DAT,7,4) for Year and MID(DAT,4,2) for Month)

Re: Sorting Date

MarcoWedel — Mon, 01 Sep 2014 09:45:18 GMT

Your DAT field is not loaded as Timestamp but as Text.

Try

Date(MonthStart(TimeStamp#(DAT, 'DD.MM.YYYY hh:mm:ss')),'MM.YYYY')

or better load your DAT field with

TimeStamp#(DAT, 'DD.MM.YYYY hh:mm:ss') as DAT

then your expression

Date(MonthStart(DAT), 'MM.YYYY')

will work too.

regards

Marco