Sorting Date Field

[niharika1234]

Hello,

I have a field called Reporting Month. It comes like below. I want to sort the Year From Asc-- like 2013, 2014, 2015...so on and Month Like Jan , feb so on. So it should come like Jan 2013, feb 2013.............Jan 2014, Feb 2014.........Jan 2015, Feb 2015 ....so on. Please suggest.

Niha

[Frank_Hartmann]

right click on field --> Sort Tab --> Sort by --> expression:

num(Date#([Reporting Month],'MMM-YYYY'))

hope his helps

[niharika1234]

Thanks Frank. I tried this . My dimension is Reporting month , a calculated dimension. =month(DUE_DATE)& '-' & YEAR (DUE_DATE). So basically it is concatenating Month and Year from Original Date. Basically I have to modify this expression I believe.

Thanks,

Niha

[Frank_Hartmann]

try this sorting expression:

num(Date#(month(DUE_DATE)& '-' & YEAR (DUE_DATE),'MMM-YYYY'))

if not, please tell me the format of DUE_DATE!

[niharika1234]

Hello,

format of Due Date is above.

[Frank_Hartmann]

this should work as sorting expression:

=num(date(date#(DUE_DATE,'DD/MM/YYYY'),'MMM-YYYY'))

[maxgro]

Try sort by expression

max(DUE_DATE)

[juraj_misina]

You can also create a new field in script:

Dual(month(DUE_DATE)& '-' & YEAR (DUE_DATE),MonthStart(DUE_DATE))     as YearMonth

and then use this field as s dimension instead of calculated dimension. This will sort numerically.

[qlikviewwizard]

Hi,

Try like this.

Data:

LOAD DATE(DATE#([Reporting Month],'MMM-YYYY'),'MMM-YYYY') as [Reporting Month] INLINE [

Reporting Month

Jan-2001

Feb-2002

Mar-2003

Apr-2004

May-2005

Jun-2006

Jul-2007

Aug-2008

Sep-2009

Oct-2010

Nov-2011

Dec-2012

Jan-2014

Feb-2015

Mar-2016

Apr-2017

May-2018

Jun-2019

Jul-2020

Aug-2021

Sep-2022

Oct-2023

Nov-2024

Dec-2025

Jan-2027

Feb-2028

Mar-2029

Apr-2030

May-2031

Jun-2032

Jul-2033

Aug-2034

Sep-2035

Oct-2036

Nov-2037

Dec-2038

Jan-2040

Feb-2041

Mar-2042

Apr-2043

May-2044

Jun-2045

Jul-2046

Aug-2047

Sep-2048

Oct-2049

Nov-2050

Dec-2051

Jan-2053

];

[passionate]

Create month year using below expression in script:

Date(monthstart(Reportingdate),'MMM-YYYY')

and use sorting expression as

num(Date(monthstart(Reportingdate),'MMM-YYYY'))