Sorting the quarters of a year

[#1]

Hi

I have a dimension  which i need to sort the values are as follows

q111 which means q1 of year 2011,q211 which means q2 of 2011...

so it is scrambled as in q111,q314,q412,q212..and so on

I would like to sort it as

q111,q211,q311,q411,q112,q212,q312,q412,q113.. and so on

Can anyone help me build the expression for the sorting

Thanks

[#2]

Hi marco, sorry for the late reply but I believe from reading the above proposed solutions the best way to do it would be by changing the script in the datamodel, I proposed some of these changes but my manager does not want to change the script he just wants me to come up with a direct sorting expression...so for now i am using a match expression

[johnca]

Have you tried one of the solutions as a calculated dimension? The following will provide what you need for the dimension, then set your expression as needed.

Dimension:

Dual(date,num(right(date,2) & right(left(date,2),1))) as date

Expression:

(your espression, such as Sum(Data)

--john

[#4]

Hi I am new to qlikview so I might be doing this wrong but my original dimension is

=Right(Period,2)&Left(Right(Period,5),2)

which I changed to

Dual(Period,num(right(Period,2)&Left((right(Period,5),2))))

but this shows up as a syntax error

[johnca]

If Period is your date field name (for 'q111','q121', etc), then just substitute Period into my suggestion a la...

Dual(Period,num(right(Period,2) & right(left(Period,2),1))) as Period

[#6]

I am sorry for not giving all the information before but "Period" originally has data  as 2012 q1,2012 q2,2012 q3 but since the requirement was to show as q112,q212 the dimension that we used is =Right(Period,2)&Left(Right(Period,5),2)

and when I use your expression on top of that

Dual(Period,num(right(Period,2)&Left((right(Period,5),2)))) as Period

it gives me a syntax error on "as"

[MarcoWedel · Accepted solution]

Hi,

given this information, you could also change my above mentioned solution into

=Dual(Right(Period,2)&Mid(Period,3,2), MakeDate(Left(Period,4), Right(Period,1)*3-2,1))

as an front end expression.

Here's an example of how using this expresion in either front or back end (supposed you could convince your manager 😉 could look like:

The big advantage of using the dual() function to seperate the presentation format from an internal numerical date value is, that sorting and using this field in date calculations does not require any additional efforts.

hope this helps

regards

Marco

[MarcoWedel]

Aliasing a field with 'as' is a backend script technique, so it's throwing an error in the front end.

[#9]

TY this worked perfectly

[MarcoWedel]

you're welcome

regards

Marco