LastFullWeek, and then week before that, and then before that, etc 1

[Woodyuk]

Im trying to do a report where I will show figures for the last 8 weeks. My first field is the following:

if {DATE} in LastFullWeek then
{QTY} else 0

This works fine. Then for the week previous to that I do:

if {DATE} in Dateadd("d",-7,minimum(LastFullWeek)) to dateadd("d",-7,maximum(LastFullWeek)) then
{QTY} else 0

Again this works fine. Now I thought it would be a case of changing the -7 in the above formula to -14 to give me the week before that, and then so on with the other weeks. However this does not work. Maybe im doing something wrong, or there is another way to do this?

Im using Crystal 10

 

[Madawc]

Try grouping by week. This should be offered automatically as an option if you group on a date field.

If that isn't suitable, try Datepart("ww", {DATE}), which will give you the week number, 1 to 53. Combined with Year(Date) it should give you your range.

Madawc Williams (East Anglia, UK)

 

[Woodyuk]

Right see that datepart your talking about. However I should of said I want this to be run automaticly. As far as I can see i would need to change the info each week?

 

[lbass]

It looks like you're trying to create a manual crosstab. Your formula should work with -14, etc. In what way did it "not work"? Did you get some kind of error message? Please be specific.

-LB

 

[Woodyuk]

I got nothing no information, just a 0.
I know this is incorrect, as the i have gone through the data manually

 

[lbass]

Please recheck your formula--this should work.

-LB

 

[Woodyuk]

Your right. My formula does work. The data i was looking at had 3 weeks worth, but that included the current week, which my report doesnt look at! DOH!

Time for a holiday, many thanks for eveyones help