Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sorting of Period

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

10 Replies

Re: Sorting of Period

Please check enclosed file...

UPDATE :

Check 2nd file also..

nizamsha
Valued Contributor

Re: Sorting of Period

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

preminqlik
Valued Contributor II

Re: Sorting of Period

Hi try with this

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

Not applicable

Re: Sorting of Period

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

Simply Change Day to Year in my Script...

Not applicable

Re: Sorting of Period

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

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...

Capture.JPG.jpg

Re: Sorting of Period

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

Hi Sivasu,

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

QlikCommunity_Thread_113285_Pic1.JPG.jpg

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

Community Browser