topic Sorting Date Field in QlikView

Sorting Date Field

niharika1234 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sorting Date Field

Frank_Hartmann — Fri, 02 Nov 2018 17:57:15 GMT

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

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

hope his helps

Re: Sorting Date Field

niharika1234 — Fri, 02 Nov 2018 18:12:55 GMT

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

Re: Sorting Date Field

Frank_Hartmann — Fri, 02 Nov 2018 18:23:08 GMT

try this sorting expression:

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

if not, please tell me the format of DUE_DATE!

Re: Sorting Date Field

niharika1234 — Fri, 02 Nov 2018 18:32:00 GMT

Hello,

format of Due Date is above.

Re: Sorting Date Field

Frank_Hartmann — Fri, 02 Nov 2018 18:37:14 GMT

this should work as sorting expression:

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

Re: Sorting Date Field

maxgro — Fri, 02 Nov 2018 19:45:55 GMT

Try sort by expression

max(DUE_DATE)

Re: Sorting Date Field

juraj_misina — Fri, 02 Nov 2018 22:38:40 GMT

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.

Re: Sorting Date Field

qlikviewwizard — Sat, 03 Nov 2018 02:18:57 GMT

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

];

Re: Sorting Date Field

passionate — Sat, 03 Nov 2018 05:16:43 GMT

Create month year using below expression in script:

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

and use sorting expression as

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

Re: Sorting Date Field

niharika1234 — Sat, 03 Nov 2018 13:46:23 GMT

Thanks to all for support. I will try all.

Re: Sorting Date Field

qlikviewwizard — Fri, 09 Nov 2018 01:14:09 GMT

Hi niharika1234

Did you try my solution?

Please mark correct answer and close the thread. Thank you.

Re: Sorting Date Field

niharika1234 — Fri, 09 Nov 2018 14:56:50 GMT

Hello Arjun,

Certainly My requirements from business changed and they want something else but this was a very good discussion and helpful.

Thanks,

Niharika