Sort date in ascending order

[udaya_kumar]

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),......

[jagan]

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.

[udaya_kumar]

Hi Jagan,

Thanks for the reply,

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

I have attached the image. please check it.

[jagan · Accepted solution]

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.

[udaya_kumar]

Thanks for the big help,

It worked

[udaya_kumar]

Only for Half Year, it is not working properly.

What is the problem in that?

[jagan]

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.

[udaya_kumar]

Hi Jagan,

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

check the attached image.

[jagan]

Hi,

Can you attach the sample file with some data.

This helps in solving the problem easily.

Regards,

Jagan.