Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

2 digit year field how to sort from hi to lo ?

Hi All

I am using below code to get 2 digit for year :-

  Right(year(date),2)           as [year_],

I am not able to sort the year in sequences from hi to lo

Any work around ?

PAul

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Paul

You can create a dual for this, and sort it numerically, like this.

Dual(Right(Year(date), 2), YearStart(date))

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

18 Replies
bill_mtc
Partner - Creator
Partner - Creator

Simply because year is now traited as Text o String format. Format your year using Date() function to retain it as Year. See line of script below:

Date(date,'YY') as [year_]

It is very important to keep year in it's original format. Hope it helps.

Thanks,

Bill

bill_mtc
Partner - Creator
Partner - Creator

Paul,

Also, to not change your original script, please see image below:

sort year.png

Not applicable
Author

Hi Bill

Hi Sir

I use

Date(date,'YY') as [year_]

i get duplicated year. any work around ?

jagan
Luminary Alumni
Luminary Alumni

Hi Paul,

Instead of this script

Right(year(date),2)           as [year_], -- Right() will give you the result in string

you use this

Date(date, 'YY') as [year_] this will give you the year in date (numeric)

Hope this helps you.

Regards,

Jagan.

tresesco
MVP
MVP

Try:

=Num(Mod(Year(date),100),'00')

You can try it in he script as well.

PFA

Not applicable
Author

hi paul may i know why you use like that

Not applicable
Author

Hi Nir

I am lazy to write code for year field :-

=

IF(year='2014','14',

IF(year='2013','13',

IF(year= '1997' ,'97',

IF(year= '1998' ,'98',

IF(year= '1999' ,'99',

IF(year= '2000' ,'00',

IF(year= '2001' ,'01',

IF(year= '2002' ,'02',

IF(year= '2003' ,'03',

IF(year= '2004' ,'04',

IF(year= '2005' ,'05',

IF(year= '2006' ,'06',

IF(year= '2007' ,'07',

IF(year= '2008' ,'08',

IF(year= '2009' ,'09',

IF(year= '2011' ,'11',

IF(year= '2012' ,'12',

IF(year= '2010','10'))))))))))))))))))

Not applicable
Author

Hi Jagan

if i use :-

Date(date, 'YY') as [year_] this will give you the year in date (numeric)


My year field unable to sort like below :-


98 99 10 11

Not applicable
Author

Hi Tres

Notice that it not able to sort . i need it :-

96  99 00 11 12