Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
Thanks Stephen. The second option is working fine for me.
A very good approach and can also be used in the chart properties to Sort the values (under Sort tab->Expression)
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
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.
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.
What would be the correct way, to format the field as appropriately formated date, if my input is YearCalendarWeek, e.g. "201715"?
Please open a new thread for this question.
Thanks
Regards
Marco