Solved: special sort of column - Qlik Community

Report Inappropriate Content · ‎2015-01-23

hi

as an example i would like to sort the following columns values

according to the period and year.

so for example the value p03 11 should come before p08 11 in the list and p12 12 should be last in the list.

it is possible to add a sorting function in Qlikview on the column for special sort?

Period

p08 11

p10 11

p03 11

p09 11

p12 12

p06 11

Best

Brad

MarcoWedel · ‎2015-01-23

in this case, one solution could be to simply load it as date using the date#() function:

LOAD Date#(Period, 'pMM YY') as Period
Inline [
Period
p08 11
p10 11
p03 11
p09 11
p12 12
p06 11
p01 12
p02 12
p01 13
p02 13
p03 13
p01 14
p02 14
];

hope this helps

regards

Marco

Anonymous · ‎2015-01-23

you should use makedate

makedate (num(right(Period,2), num(mid(Period,1,2) as Datefield

you get the numbers year and month from your field Period

using makedate you make a datefield which you can sort

MarcoWedel · ‎2015-01-23

has the list to be ordered first by year or by period?

Report Inappropriate Content · ‎2015-01-23

first by period then year

alexandros17 · ‎2015-01-23

in the sort tab of your page check the box "TExt"

let me know

Anonymous · ‎2015-01-23

makedate requires 4 Digit for year

so use

makedate (2000+num(right(Period,2), num(mid(Period,1,2)) as Datefield

Report Inappropriate Content · ‎2015-01-23

thanks Rudolf.

however i would like to present the field as it is t ex with the leading p? can i do this in the interface?

Best

Brad

Anonymous · ‎2015-01-23

sure, so convert it the date to text wioth preceding "P" and

in the sort tab you sort by mid(Datefield,1,6)

jaygarcia · ‎2015-01-23

Hi Bradley

Actually there's nothing special in the sorting of Period. If you choose "Text" and from A->Z will do the work

Hope it makes sense,

J

MarcoWedel · ‎2015-01-23

default order for this field (by text) should be all right then!?!?

regards

Marco