8 Replies Latest reply: Sep 7, 2012 8:56 AM by jagan mohan rao appala

# Sort date in ascending order

Hi,

I am using the below code in the Edit Script to get the quarter, half year and yearly data for the report.

'Quarter' & Ceil(Month(MONTH_DATE)/3) & '(' & Year(MONTH_DATE) & ')' AS DATE_DIM.QuarterYear,

'HalfYear' &
Ceil(Month(MONTH_DATE)/6) & '(' & Year(MONTH_DATE) & ')' AS DATE_DIM.HalfYear,

'Year' &
Ceil(Month(MONTH_DATE)/12) & '(' & Year(MONTH_DATE) & ')' AS DATE_DIM.Year

These date fields are not sorting in order, because of the text Quarter or HalfYear in the field.

I have attached the image with this, please check the field that how the field is sorting.

so how can we sort it in ascending order?

the image shows the sorting as

HalfYear1(2010), HalfYear1(2008),HalfYear1(2007),...

But i want the sort order like

HalfYear1(2006),HalfYear2(2006),HalfYear3(2006),HalfYear4(2006),HalfYear1(2007),......

• ###### Re: Sort date in ascending order

Hi Udaya,

Try using the Dual function for this type of scenarios

For example:

Dual( 'Quarter' & Ceil(Month(MONTH_DATE)/3) & '(' & Year(MONTH_DATE) & ')', MONTH_DATE) AS DATE_DIM.QuarterYear,

Dual( 'HalfYear' & Ceil(Month(MONTH_DATE)/6) & '(' & Year(MONTH_DATE) & ')'

, MONTH_DATE)

AS DATE_DIM.HalfYear,

Dual( 'Year' & Ceil(Month(MONTH_DATE)/12) & '(' & Year(MONTH_DATE) & ')'

, MONTH_DATE)

AS DATE_DIM.Year

Now in sort section Select Numeric value instead of string.

Hope this helps you.

Regards,

Jagan.

• ###### Re: Sort date in ascending order

Hi Jagan,

I tried the code which you gave, but it is showing the values multiple times.

I have attached the image. please check it.

• ###### Re: Sort date in ascending order

Hi,

Try this

Dual( 'Quarter' & Ceil(Month(MONTH_DATE)/3) & '(' & Year(MONTH_DATE) & ')', QuarterName(MONTH_DATE)) AS DATE_DIM.QuarterYear

Dual( 'HalfYear' & Ceil(Month(MONTH_DATE)/6) & '(' & Year(MONTH_DATE) & ')'

, Year(MONTH_DATE))

AS DATE_DIM.HalfYear,

Dual( 'Year' & Ceil(Month(MONTH_DATE)/12) & '(' & Year(MONTH_DATE) & ')'

, MonthEnd(MONTH_DATE))

AS DATE_DIM.Year

Hope this helps you.

Regards,

Jagan.

• ###### Re: Sort date in ascending order

Thanks for the big help,

It worked

• ###### Re: Sort date in ascending order

Only for Half Year, it is not working properly.

What is the problem in that?

• ###### Re: Sort date in ascending order

Hi,

Try this

Dual( 'HalfYear' & Ceil(Month(MONTH_DATE)/6) & '(' & Year(MONTH_DATE) & ')'

, Year(If(Month(MONTH_DATE) <= 6, MakeDate(Year(MONTH_DATE),6,1),

MakeDate(Year(MONTH_DATE),12,1))

))

AS DATE_DIM.HalfYear,

Hope this helps you.

Regards,

Jagan.

• ###### Re: Sort date in ascending order

Hi Jagan,

I tried it, but it is giving only one half year per year like HalfYear1(2006),HalfYear2(2007),......

check the attached image.

• ###### Re: Sort date in ascending order

Hi,

Can you attach the sample file with some data.

This helps in solving the problem easily.

Regards,

Jagan.