Sort within the formula? 2

[cjbrown815]

Hi,
I have a line graph that is changing on a formula field. the formula field is pulling a datetime and a load number (string). The formula is:

ToText({SAMPLE.DATE1}, "MMM/dd/yyyy")& " " & " " & {SAMPLE.TEXT3}

the problem I'm having is getting the graph to sort by the actual datetime stamp (without showing it). It's sorting by the new format (Oct, Sept, July, Jan, Aug)
I thought if I put a sort by SAMPLE.DATE1 right in the formula I can get it to sort correctly. I want the X axis to sort from the left by the earliest date for a trend graph.

How can I get this to display as it is but sort correctly?

Thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Hi,

You converted the date (a NUMBER) to a STRING and it will sort...
[tt]
Apr Aug Dec Feb Jan Jul Jun Mar May Nov Oct Sep
[/tt]
in colating sequence.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

That's right. Now I need it to sort in the correct order by date from left to right starting with the earliest date, thoughts?

Thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Format the string the want that you want to sort.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

If I do just the raw values I get something like

11/11/2009 12:00 123456 (datetime & lot number)
{SAMPLE.DATE1} & " " & " " & {SAMPLE.TEXT3}


I'd like to just trim the time so I have just the date and lot.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

Instead of using the advanced chart option, select the numeric axis chart option in chart expert->type. Then on the right, select Date Axis Line Chart. Create the chart and afterwards, select the axis label->right click->format axis label->number->select the date format you like.

-LB

 

[SkipVought]

If you need to have the TEXT3 value then change the ToText format to sort the way your want...
[tt]
ToText({SAMPLE.DATE1}, "yyyy/mm/dd")& " " & " " & {SAMPLE.TEXT3}
[/tt]
NOTICE: Not MMM but MM, as this is a TEXT sort.

If you don't need TEXT3, proceed as LB suggests.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

lbass - it seems to be getting confused by the formula, it's returning only dates, but the year is 1899 and 1900. It also put the date - lot results in the body of the graph.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

Skip - When I do the mm as you recommend it returning

2009/00/19 0519323

It seems to be replacing the month with 00



-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

A tweak to Skip's formula:

ToText({SAMPLE.DATE1}, "yyyy/[red]MM[/red]/dd")& " " & " " & {SAMPLE.TEXT3}

Ignore my earlier suggestion--I was thinking you were only graphing on date--sorry.

-LB

 

[cjbrown815]

Thanks for the help, the tweak worked.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931