topic Re: Sorting of Period in QlikView

Sorting of Period

Sat, 05 Apr 2014 07:55:29 GMT

Hi all,

I am facing problem while sorting the Period.

For Example, I have period field as AUG 11 AUG 12 AUG 13 DEC 11 DEC 12 DEC 13 etc for all the month.

While bringing it in qlikview , i am not able to sort it.

I need a following sorted in qlikview , if i build any report in qlikview .

JAN 11 FEB 11 MAR 11 APR 11 MAY 11 JUN 11 JUL 11 AUG 11 SEP 11 OCT 11 NOV 11 DEC 11

JAN 12 FEB 12 MAR 12 APR 12 MAY 12 JUN 12 JUL 12 AUG 12 SEP 12 OCT 12 NOV 12 DEC 12

JAN 13 FEB 13 MAR 13 APR 13 MAY 13 JUN 13 JUL 13 AUG 13 SEP 13 OCT 13 NOV 13 DEC 13.

Kindly do the needful

Regards,

Sivasu

Re: Sorting of Period

MK_QSL — Sat, 05 Apr 2014 08:00:47 GMT

Please check enclosed file...

UPDATE :

Check 2nd file also..

Re: Sorting of Period

nizamsha — Sat, 05 Apr 2014 08:01:57 GMT

use dual to put do in backend

if(Month='Jan11',dual('Jan11',1),

if(Month='Feb11',dual('Feb11',2),

if(Month='Mar11',dual('mar11',3), etc like ths

Re: Sorting of Period

preminqlik — Sat, 05 Apr 2014 08:30:19 GMT

Hi try with this

=only({1}date(date#(Capitalize(trim(Period)),'MMM YY'))

Re: Sorting of Period

Sat, 05 Apr 2014 08:34:27 GMT

Manish,

JAN-Month

11-Year(2011)

12-Year(2012)

13-Year(2013)

I don't have Date field in my template. only year and month

Re: Sorting of Period

MK_QSL — Sat, 05 Apr 2014 08:38:08 GMT

Simply Change Day to Year in my Script...

Re: Sorting of Period

Sat, 05 Apr 2014 08:46:51 GMT

Manish,

I have changed that too.. i am getting only in alaphabetical sort order.(because the Text (A-Z) is ticked in Sorting tab.)

Sivasu

Re: Sorting of Period

MK_QSL — Sat, 05 Apr 2014 09:01:03 GMT

SCRIPT:::

Load

Date#(Period,'MMM DD') as Period,

Right(Date#(Period,'MMM DD'),2) as Year,

Sales

Inline

[

Period, Sales

JAN 11, 100

FEB 11, 120

MAR 11, 122

APR 11, 130

JAN 12, 109

FEB 12, 110

MAR 12, 90

APR 12, 98

JAN 13, 80

FEB 13, 75

MAR 13, 70

APR 13, 99

];

=======================

Go to sort tab...

Re: Sorting of Period

MarcoWedel — Sun, 06 Apr 2014 23:19:48 GMT

test data

Month
JAN 11
FEB 11
MAR 11
APR 11
MAY 11
JUN 11
JUL 11
AUG 11
SEP 11
OCT 11
NOV 11
DEC 11
JAN 12
FEB 12
MAR 12
APR 12
MAY 12
JUN 12
JUL 12
AUG 12
SEP 12
OCT 12
NOV 12
DEC 12
JAN 13
FEB 13
MAR 13
APR 13
MAY 13
JUN 13
JUL 13
AUG 13
SEP 13
OCT 13
NOV 13
DEC 13

Re: Sorting of Period

MarcoWedel — Sun, 06 Apr 2014 23:32:06 GMT

Hi Sivasu,

to explain the difference between sorting dates as text fields and dates as numerical fields:

LOAD
  Month as MonthText,
  Date#(Month, 'MMM YY') as MonthNum
FROM [http://community.qlik.com/thread/113285]
(html, codepage is 1252, embedded labels, table is @1);

regards

Marco

Re: Sorting of Period

MarcoWedel — Thu, 24 Apr 2014 08:31:28 GMT

Please close this thread if your problem is solved.

thanks

regards

Marco