Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sort Datetime


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

11 Replies
amit_saini
Master III
Master III

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

Not applicable
Author

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
Not applicable
Author

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
Master III
Master III

Use this expression in sort tab

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

Anonymous
Not applicable
Author

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

num(datefield) as SortDate



bumin
Partner - Creator II
Partner - Creator II

Hi Deepa,

you can define your sort order exactly with a inine table

LoadorderMonths:

LOAD * INLINE [

    Months

   OCT

  NOV

  DEC

  JAN

];

regards

Bumin

Not applicable
Author

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