topic Re: Sort Datetime in QlikView

Sort Datetime

Mon, 05 Jan 2015 07:28:43 GMT

Hello,

There is a date field, loaded in the format YYYYMMDDHHmmSS

I am using this field in a drilldown group as dimension to display month, week and day

Expressions used are

=month(date#(Mid([datefield],1,8),'YYYYMMDD'))

=Week(date#(Mid([datefield],1,8), 'YYYYMMDD'))

= date(date#(Mid([datefield],1,8), 'YYYYMMDD'),'MM/DD/YYYY')

There is data from Oct 2014 to Jan 2015,

currently I am sorting with numeric option, so it displays Jan first. (Jan, Oct, Nov, Dec)

But actually I want to sort it in below order

Oct

Nov

Dec

Jan

Please suggest how can I do this.

Thanks,

Deepa

Re: Sort Datetime

amit_saini — Mon, 05 Jan 2015 07:32:23 GMT

Hi Deepa,

In sort inside expression do something like below as per ur required order.

Dual(Month,Match(Month,'jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec'))

Thanks,

Re: Sort Datetime

Mon, 05 Jan 2015 07:36:03 GMT

Hi Amit,

Thx for the reply,

If I do that,what ever the year it sorts it in Jan,Feb,...Dec order.

But I want it to consider the year ans month both in sorting

ex: if I select date range Oct2014 to Jan 2015

it shud be in the order Oct , Nov, Dec Jan

Re: Sort Datetime

Anonymous — Mon, 05 Jan 2015 10:24:08 GMT

Hi,

you should add in your load script an ID field for each datefield (maybe with autonumber() instruction that "labels" every value of datefield with a progressive number).

Then you can insert the ID field in the expression of sort tab.

Hope this helps,

Elena

Re: Sort Datetime

rubenmarin — Mon, 05 Jan 2015 10:31:26 GMT

Hi Deepa, you can sort your months using an expression like Min({1} datefield)

Re: Sort Datetime

anbu1984 — Mon, 05 Jan 2015 10:34:54 GMT

Use this expression in sort tab

date#(Mid([datefield],1,8), 'YYYYMMDD')

Re: Sort Datetime

Anonymous — Mon, 05 Jan 2015 10:35:39 GMT

in the Script generate a numeric value for your date column and the use that column for the sorting.

num(datefield) as SortDate

Re: Sort Datetime

bumin — Mon, 05 Jan 2015 10:36:45 GMT

Hi Deepa,

you can define your sort order exactly with a inine table

LoadorderMonths:

LOAD * INLINE [

Months

OCT

NOV

DEC

JAN

];

regards

Bumin

Re: Sort Datetime

Mon, 05 Jan 2015 11:46:37 GMT

Hi Deepa,

Try this,

While loading, sort the date field (in script using order by) then sort month by Loaded Order.

Thanks

Harsha

Re: Sort Datetime

MarcoWedel — Mon, 05 Jan 2015 13:15:44 GMT

please post sample app

Re: Sort Datetime

ecolomer — Mon, 05 Jan 2015 13:33:35 GMT

You can use to sort by month:

Num(Month(date#(Mid([datefield],1,8),'YYYYMMDD')), '00')

Re: Sort Datetime

v_iyyappan — Mon, 05 Jan 2015 13:46:47 GMT

Hi Deepa,

Please find the attached file.

Hope it will help to you.

Regards,