Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking into last 4 months from today, dat is jan,dec,oct and nov in a pivot table. But I am seeing sorting order as jan,oct,nov,dec. I nned to see oct,nov,dec and jan bcos jan is 2013 so I need that to see at last. Thanks
that is the correct behavior when sorting by month, consider using MonthName(Date) instead which will add the year to the field so it will sort as Oct 2012, Nov 2012, Dec 2012, Jan 2013
that is the correct behavior when sorting by month, consider using MonthName(Date) instead which will add the year to the field so it will sort as Oct 2012, Nov 2012, Dec 2012, Jan 2013
I have Date field as 1/24/2013 format, I used Date(Date field,'MMM-YY') and converted into Jan-13, but in pivot table when I used this field I am still unable to sort. ...
is the sort set to numeric value in properties?
Yes, I did...
Yes, I did...Please find the attachment
The first thing you need to do is to interpret the dates as dates. They are not identified as dates now. This is done with the Date#() function. And you should do that before you create the qvd. See more on http://community.qlik.com/docs/DOC-3102.
Then You can create the Months using Month(Date#([DateField],'MMM-YYYY')).
HIC
Your Field name [Curative TR Month Year] isn't a date datatype, in your script replace:
Month(CURATIVE_TR_HCSENTDTE)&'-'& Year(CURATIVE_TR_HCSENTDTE) as [Curative TR Month Year],
With:
MonthName(CURATIVE_TR_HCSENTDTE) as [Curative TR Month Year],
Pramod,
There is a quite simple solution as an alternative to the above.
=dual(MonthField,Index('janfebmaraprmayjunjulaugsepoctnovdec',lower(MonthField)))
Suppose your MonthField contains Mar, Dec, Jan, Feb ... etc. in an unsorted order. If you enter the formula above as a sort expression, your sequence will turn out right. It is also useful when you use Month(Fieldname) as it can distort the sort order.
Hope this adds new insight!