Last Week Sales

building reports crystal reports
1

Hi I have a sales table i.e.

weekNo sales shop

 1               5          14
 1               6          15
 1               8          16
 2               3          14
 2               9          15
 2               6          16

so I need a report that shows this weeks sales and last week sales. the user enters a parameter from this week ie week 2 in this case.

I have figured out that for ales this week in my report I need a function that says ‘if weekNo = week param then sales else 0’ and I sum this function up.

Now for last weeks sales and this is my question. I cannot just subtract 1 from the week Param to get last week (incase it is year end).

What Should I do?[

vagasv1 (BOB member since 2007-10-10)

2

Cant you just check if the week is 1, then the previous week will be 52. Otherwise its just currentweek -1?

ABILtd :uk: (BOB member since 2006-02-08)

3

no, some years have 53 weeks

vagasv1 (BOB member since 2007-10-10)

4

Andy,

Could you advise me on the best way to proceed. I could create a table with ‘week Number’ and ‘Week No Start Date’ in it.

ie

WeekNumber WeekNumberStartDate

1 04/01/09
2 11/01/09
3 19/01/09

But ~I am not sure how this would help me.

thanks Paul

vagasv1 (BOB member since 2007-10-10)

5

I think I might of solved this. I have created a table with the week numbers and WeekStartDate as 2 seperated columns. I have then linked the tables together on week number so I get:

weekNo sales shop WeekStartDate

1 5 14 04/01/09
1 6 15 04/01/09
1 8 16 04/01/09
2 3 14 11/01/09
2 9 15 11/01/09
2 6 16 11/01/09
3 9 14 18/01/09
3 4 15 18/01/09

so to find the sales for previos week I now have a formular the says:

if {WeekStartDate}= DateAdd ("ww",-1 , Maximum ({WeekStartDate})) then Sales else 0

However I cannot sum this. It is saying 'Cannot summerize this formular

I am scratching my head so much its started to bleed.

Please help, Many thanks

vagasv1 (BOB member since 2007-10-10)