topic Re: Formatting Quarter and Year - sorting in QlikView

Formatting Quarter and Year - sorting

Anonymous — Wed, 10 Jul 2013 20:42:08 GMT

I'm receive the following from the source instead of a date:

Q1 2012

Q2 2012

Q3 2012

Q4 2012

Q1 2013

I'm pretty sure this is the load order too. But when I try to sort in the pivot object I'm seeing a different order.

I'm trying to show sales by product for last 5 quarters in a line chart. Product is the other dimension used.

Any help is appreciated...

Re: Formatting Quarter and Year - sorting

stephencredmond — Wed, 10 Jul 2013 21:27:51 GMT

Two options.

1. Reverse the order of Year and Q:

Right(YQ, 4) & ' ' & Left(YQ, 2) As YQ,

This will now be in dictionary order.

2. If you really need to keep the old formatting:

Dual(YQ, Right(YQ, 4) & Mid(YQ, 2, 1)) As YQ,

You will have the same format but you can sort them numerically.

Regards,

Stephen

Stephen Redmond is author of QlikView for Developer's Cookbook

He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.

Follow me on Twitter: @stephencredmond

Re: Formatting Quarter and Year - sorting

Mon, 09 Jun 2014 16:44:52 GMT

Thanks Stephen. The second option is working fine for me.

Re: Formatting Quarter and Year - sorting

montubhardwaj — Thu, 27 Oct 2016 20:49:23 GMT

A very good approach and can also be used in the chart properties to Sort the values (under Sort tab->Expression)

Re: Formatting Quarter and Year - sorting

johnw — Thu, 27 Oct 2016 21:12:59 GMT

I prefer all my date-like fields be appropriately-formatted dates. Then you can use all the normal date functions if you need them. In the case of quarters, I like to assign the first day of the quarter. I think like this?

dual(YQ,makedate(right(YQ,4),mid(YQ,2,1)*3-2)) as YQ

Edit: fixed

Re: Formatting Quarter and Year - sorting

MarcoWedel — Thu, 27 Oct 2016 23:58:32 GMT

Quite an old thread to continue, but I think your suggestion to create proper dates is the best solution here.

I like your expression to create a dual quarter name field out of the given strings, I only guess the quarter number additionally has to be transformed into a month number e.g. using "QuarterNum*3-2" for the makedate() function to work properly in this case.

Re: Formatting Quarter and Year - sorting

johnw — Fri, 28 Oct 2016 00:21:09 GMT

Oops, didn't realize the thread had been necro'd!

And oops, I had the *3-2 on my computer, but failed to copy it when posting. I'll fix it just in case someone references it later.

Re: Formatting Quarter and Year - sorting

chriys1337 — Mon, 10 Apr 2017 13:50:14 GMT

What would be the correct way, to format the field as appropriately formated date, if my input is YearCalendarWeek, e.g. "201715"?

Re: Formatting Quarter and Year - sorting

MarcoWedel — Tue, 11 Apr 2017 10:25:39 GMT

Please open a new thread for this question.

Thanks

Regards

Marco