18 Replies Latest reply: Sep 2, 2014 3:55 PM by Marco Wedel

# Sorting the quarters of a year

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

• ###### Re: Sorting the quarters of a year

This should do it...

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

HTH,

John

• ###### Re: Re: Sorting the quarters of a year

The easiest way, and the best performing to do that is creating a numeric value in your calendar table so for each value in the for of "Q111" corresponds an integer "1":

```Quarter - QID
Q111 - 1
Q211 - 2
Q311 - 3
```

Then in the chart use this QID field to sort, although you use the Quarter field as dimension.

Miguel

• ###### Re: Sorting the quarters of a year

Miguel, your comment to my solution (and the other solutions presented here) got me thinking about how much is too much for a document and dashboard content further down the road. As my answer does exactly as the poster requested it is also very short and simple, and it too creates a numeric value that can be sorted in any dimension or expression (and IMO, even simpler than yours :-) ). How much 'extra' does a solution to a problem need to be, and how much should a developer design into an application to anticipate growth? This is a whole 'nother topic of course...and BTW, love your book(s).

--john

• ###### Re: Sorting the quarters of a year

Hi

You can use following expression in your Sorting:

=right(Quarters,3)

Kindly, replace your quraterfield name with Quraters in the above expression.

See the Snapshot:

Also, see the Attachment.

Hope, that helps.

Regards

Aviral Nag

• ###### Re: Sorting the quarters of a year

While Creating your Quarter Field in Script, use below DUAL function... Now wherever you have used Quarter Field, you can just sort by Quarter Numeric Ascending or Descending Order

Dual('q'&Ceil(Month(Date)/3)&Right(Year(Date),2),Right(Year(Date),2)&Ceil(Month(Date)/3)) as Quarter;

• ###### Re: Sorting the quarters of a year

PFA

• ###### Re: Sorting the quarters of a year

Hi,

one solution:

instead of creating a dedicated sorting expression for each and every usage of this dimension, I would instead load it as a Date with a display format as you specified. Sorting would then require no additional effort:

```tabQuarters:
Dual(Quarter, MakeDate(Right(Quarter,2)+2000, Mid(Quarter,2,1)*3-2, 1)) as Quarter
Inline [
Quarter
q111
q211
q311
q411
q112
q212
q312
q412
q113
q213
q313
q413
q114
q214
];

```

hope this helps

regards

Marco

• ###### Re: Sorting the quarters of a year

Try something like this....

Take the Quarter as dimension.

Sort the Quarter dimension in sort tab

a) Based on "Year"

b) And enable the 'Text' option and choose ascending order.

• ###### Re: Sorting the quarters of a year

Any luck testing the proposed solutions so far?

Thanks

Regards

Marco

• ###### Re: Sorting the quarters of a year

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

• ###### Re: Sorting the quarters of a year

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:

--john

• ###### Re: Sorting the quarters of a year

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

• ###### Re: Sorting the quarters of a year

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

• ###### Re: Sorting the quarters of a year

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"

• ###### Re: Sorting the quarters of a year

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

• ###### Re: Sorting the quarters of a year

TY this worked perfectly

• ###### Re: Sorting the quarters of a year

you're welcome

regards

Marco

• ###### Re: Sorting the quarters of a year

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