Week starting on Monday - then graphing last 6 months into 2013

[adriae]

I have a weekly report that shows the last 6 months' orders totals in a line chart, by week. These weeks span Monday-Sunday, and the formula I used to create the Monday-start weeks was:

DatePart ("ww", {Orders_Info_BE.OrderCreateDate}, 2)//where the "2" makes Monday the starting date

After grouping on this formula, I changed the group display name by using another formula to make the date display appropriately in the line chart ( instead of showing 1,2,3,4 as the week number, I wanted to see the starting date of the data week):

totext({Orders_Info_BE.OrderCreateDate}-Dayofweek({Orders_Info_BE.OrderCreateDate},2)+1,"MM/dd/yyyy")

Now it's the second week in January 2013, and my line chart is displaying the first and second week of this year at the very beginning of the chart, followed by the other previous months from last year. I'm sure this has something to do with the display formula above that sets the date display to text.

How can I rewrite the group name display formula so that the chart will display the weeks in chronological order, correctly?

Thank you,
Adria

 

[SkipVought]

hi,

If you are reporting "by week" then why not have a 26 week range, rather than a 6 month range???

Since you are converting TO TEXT, your TEXT DATE COLATION will not be in chronological order!!!
[tt]
totext({Orders_Info_BE.OrderCreateDate}-Dayofweek({Orders_Info_BE.OrderCreateDate},2)+1,"yyyy/MM/dd")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

If you used format field->"display string" to change your group name, it would not affect the date order.

-LB

 

[adriae]

Thanks Skip, your suggestion worked perfectly.

 

[adriae]

One more issue has come up with this weekly start on Monday issue....

I have been able to successfully group on weeks beginning with Monday, and was also able to change the display from "YYYY/mm/dd" to "M/dd/yy" and it looks great in most of my graphs.

I have a couple of graphs, though, where I need to chart on one of my created formulas (such as a calculated percentage of two separate summaries) rather than a simple summary from the report. When I go to Chart Expert, Data, Advanced, and then select my @Weekly start on Monday formula as one of the "On change of" criteria, it will not display my weekly groups as "M/dd/yy" in the actual graph like I need them to. It displays them the way they are truly grouped in the report, which is "YYYY/mm/dd" so that they will display in the correct chrono. order. I have searched everywhere and cannot find an option to change that group display for the graph.

Any suggestions???
Thank you!