Sort Datetime

[#1]

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

[amit_saini]

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,

AS

[#3]

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

[Anonymous]

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

[rubenmarin]

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

[anbu1984]

Use this expression in sort tab

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

[Anonymous]

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

num(datefield) as SortDate

[bumin]

Hi Deepa,

you can define your sort order exactly with a inine table

LoadorderMonths:

LOAD * INLINE [

    Months

   OCT

  NOV

  DEC

  JAN

];

regards

Bumin

[#9]

Hi Deepa,

Try this,

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

Thanks

Harsha

[MarcoWedel]

please post sample app