Line chart with label displayed as Mon-YYYY, but sorted correctly

[gundley]

Hi,

I am trying to display the x axis label for a line chart as Mon-YYYY (Jan-2006 Feb-2006 etc).

However I also want the labels to be sorted correctly.
which means it should be Jan-2006, Feb-2006, Mar-2006 etc.

The problem is that since the fields are in text format, it sorts differently than when it is a date field.


Does anybody know how I can achieve both (sort it by month , but display it in text)

Any help is appreciated on this.

(remember this is a line chart)


thanks very much

 

[lbass]

Create a formula that returns the string label as you wish to see it, as in:

if year({table.date}) = 2006 then
(
if month({table.date}) = 1 then "Jan-2006" else
if month({table.date}) = 2 then "Feb-2006" else
if month({table.date}) = 3 then "Mar-2006" else
//etc.
) else
if year({table.date}) = 2007 then
(if month({table.date}) = 1 then "Jan-2007" else//etc.

Then use this as your "on change of" field, and after entering it, select it so it is highlighted->order->choose specified order and then select the text from the dropdown in the order you want it displayed.

-LB

 

[gundley]

thanks lbass.

But dont you think that this is hard coding the months.

The Month range in my report goes all the way to 1995.
Also the report should be usable in future.

Do I write a huge formula with a ton of lines?

It that is the only way, I guess I will do it.

thanks

 

[lbass]

Sorry, yes, a better suggestion is:

monthname(month({Orders.Order Date}),true)+'-'+totext(year({Orders.Order Date}),"0000")

Then use specified order on this formula.

-LB

 

[mocgp]

I am interpreting your first post to mean that your date fields are text type (i.e., "20060101"). If that is the case, then you need to convert them to date fields before lbass's formula will work (which it does nicely). If so, here is your conversion formula:


Global StringVar dte:=
mid({Orders.Order Date},5,2)+"/"+
right({Orders.Order Date},2)+"/"+
left({Orders.Order Date},4);
If isdate(dte) then datevalue(dte)

Insert this formula in place of {Orders.Order Date} in his statement.

 

[mocgp]

I forgot to add to the previous post: In the change group options, select "in ascending order" (not specified order) then in the "This section will be printed..." option, select "for each month

 

[gundley]

thanks guys.

mocgp,

I am creating a line graph using the chart expert.

I bet you are talking about using the group expert.

Can I use group expert with chart expert.

thanks