I'm receive the following from the source instead of a date:
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...
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.
Stephen Redmond is author of QlikView for Developer's Cookbook
Follow me on Twitter: @stephencredmond
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
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.