Formatting Quarter and Year - sorting

[Anonymous]

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...

[stephencredmond]

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

[#3]

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

[montubhardwaj]

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

[johnw]

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

[MarcoWedel]

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.

[johnw]

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.

[chriys1337]

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

[MarcoWedel]

Please open a new thread for this question.

Thanks

Regards

Marco