YTD Average by Week

[FireGeek21]

I need to show a YTD average by week. I can get a weekly average but I can't seem to average year to date and show what that YTD average looked like each week. Average is amount of time it takes to get a task done.

For example:

Week 1 average = 45 YTD average at week 1 should be 45
Week 2 average = 55 YTD average at week 2 should be 50
Week 3 average = 40 YTD average at week 3 should be 45
Week 4 average = 60 YTD average at week 4 should be 50

This is for a fiscal year that starts 7/1. I also need to display the weekly YTD average across in a crosstab so it can be exported in Excel. (WEEK 1 | WEEK 2 | WEEK 3 | Week 4 | ...etc...)

THANKS!!!

Tammy

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[Charliy]

Do you want to start each week on a Sunday, or is July 1-7 week one?

 

[FireGeek21]

Right now it is Monday - Sunday. I am flexible on this.

Thanks

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

Does anyone have any thoughts on how to do this? Ideally, I would like to put it in a crosstab.

Thanks

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[Charliy]

datediff('w',{@startdate},{table.datefield})

Will give you a filed that you can group by. The Start Date should be the same day of week as you want your weeks to break out by.

 

[FireGeek21]

Charliy,

I have done this to get the number of days and then the average. I am beyond this. I need a rolling average - an average that shows what the YTD average is every week. "What is the ytd average last week? What is the ytd average this week? etc." for all weeks in a fiscal year that starts 7/1.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[Charliy]

Once you're grouped by week just set up Running Totals for your amounts and weeks, have them not reset.
Have another running total that resets on change of group to give you the weekly total.
Then just divide the count by the number of weeks

 

[FireGeek21]

Running totals can't be used in a cross tab though. I need the weeks to display across.

Thoughts?

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)