topic Re: Sort date in ascending order in QlikView

Sort date in ascending order

udaya_kumar — Fri, 07 Sep 2012 07:27:26 GMT

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

jagan — Fri, 07 Sep 2012 07:47:00 GMT

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

udaya_kumar — Fri, 07 Sep 2012 09:31:38 GMT

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.

Re: Sort date in ascending order

jagan — Fri, 07 Sep 2012 10:08:13 GMT

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

udaya_kumar — Fri, 07 Sep 2012 10:35:11 GMT

Thanks for the big help,

It worked

Re: Sort date in ascending order

udaya_kumar — Fri, 07 Sep 2012 10:38:10 GMT

Only for Half Year, it is not working properly.

What is the problem in that?

Re: Sort date in ascending order

jagan — Fri, 07 Sep 2012 12:13:24 GMT

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

udaya_kumar — Fri, 07 Sep 2012 12:43:20 GMT

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

jagan — Fri, 07 Sep 2012 12:56:22 GMT

Hi,

Can you attach the sample file with some data.

This helps in solving the problem easily.

Regards,

Jagan.