Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
This is my 1st post so I hope im not doing anything wrong!
Im importing some data with the column labelled "Month" and the data in there is in text as Jan, Feb, Mar, etc but when I try and put this into a pivot table it comes out as Apr, Aug, Dec .....
Is there a way so I can sort this in the correct order?
Thanks in adavance
Mark
Best approach is to create your Month values as a dual that you can sort numerical:
LOAD
Month(Date#(MonthField,'MMM')) as MonthField,
MonthField being your field containing your month values.
Thanks for the quick reply.
Unforntunatly its pulling the info from a database I cant change. Is what Im looking for is another column called "year" and I want that going down the left hand side of the pivot table then have the months going across the top.
Is there work around that can be used to achive this, somehing like the sort by custom lists in Excel?
I always use a inline table to assign a numeric value to the months:
MESI:
LOAD * INLINE [
MONTH, MESE_NUM
jan,1
feb,2
mar,3
apr,4
may,5
jun,6
jul,7
aug,8
sep,9
oct,10
nov,11
dec,12
];
And then sort by numeric value on charts/Tables/Listboxs
Cheers
Juan Pedro
You can use a sort expression in sort tab for your dimension field:
=match(MonthField, 'Jan', Feb','Mar',...)
Thanks for the reply Juan, Im very new to this so I'm not sure how to use the code you've give me correctly?
Thanks for this it seems to be working!
If your original Month field contains values according to the MonthNames variable in your QlikView document, you only need to configure the month sort order in your pivot as number ascending.
This because Month become a dual field.
If you need to understand the type of a field, you can see the tags in the field properties (Ctrl+Alt+D and find Month field in the Table tab).
See attached example
Is a Inline Table so should be inserted in your load script.
Since you are new this group may content some usefull guides/examples for your formation.
http://community.qlik.com/groups/qlikview-academic-program
Cheers,
Juan Pedro
Thanks both, Im going to be in meetings the rest of the day so I will take a look tomorow but thanks again for you help