Solved: Re: Sorting the quarters of a year - Qlik Community

Report Inappropriate Content · ‎2014-08-09

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

MarcoWedel · ‎2014-09-02

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

View solution in original post

johnca · ‎2014-08-10

This should do it...

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

HTH,

John

aveeeeeee7en · ‎2014-08-10

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

MK_QSL · ‎2014-08-10

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;

Miguel_Angel_Baeyens · ‎2014-08-10

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

And so on. RowNo() can help you in the script.

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

Miguel

qlikpahadi07 · ‎2014-08-10

PFA

MarcoWedel · ‎2014-08-10

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:
LOAD
  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

johnca · ‎2014-08-10

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

PradeepReddy · ‎2014-08-11

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.

MarcoWedel · ‎2014-08-15

Any luck testing the proposed solutions so far?

Thanks

Regards

Marco