Same Week Last Year formula 2

[blueboyz]

I'm using Crystal v9 with Peachtree 2008-Pervasive SQL database.

I need to create a column to display the values for GL Account numbers for the same week last year as the week entered in Start Date and End Date parameters.

The user enters the {?startdate}, for example of 11/19/07 and {?enddate} as 11/25/07 - the day of the week needs to start on Monday and end on Sunday. If this is week 47 this year, then week 47 for last year should be displayed on the report.

I need to show for that same week last year what the amount was each of my GL account numbers. Last year, the week would have been 11/20/06 to 11/26/06.

(11/19/07 to 11/26/07) (11/20/06 to 11/26/06)
Account This Year Last Year
Telephone 200 150
Travel 2000 4000

How can I create a formula to extract the right amounts?

 

[Madawc]

Try

DateAdd("y", -1, {?startdate})

And the same for end date.

If that doesn't work, you might need to break up the date, use DatePart to split day, month and year, subtract 1 from year and re-constitute as a Date.

Once you have the values, actually showing them the way you want might be more tricky. A crosstab might do it, failing that a Mock-Crosstab or possibly columns.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[goranm]

Hi!
Another approach that you can try.

Create two formulas
@LastYearStartDate
DateAdd("ww", -52, {?startdate});

@LastYearEndDate
DateAdd("ww", -52, {?enddate});

Let your Selection Formula look like:
(({Table.field} >= {?startdate} and {Table.field} <= {?enddate}) or
({Table.field} >= @LastYearStartDate and Table.field} <= @LastYearEndDate))

Create two other formulas:
@ThisYearAmount
if {Table.field} >= {?startdate} then
Table.Amount
else
0

@LastYearAmount
if {Table.field} < {?startdate} then
Table.Amount
else
0

Then insert a group on your Account.
Insert a summary field on @ThisYearAmount and on @LastYearAmount for the Account group

/Goran

 

[blueboyz]

I will try these formulas. Thank you very much!

 

[lbass]

Just a tweak. For dateadd, to reference a year you would use:

dateadd("yyyy",-1,{?startdate})

A single "y" refers to day of year.

-LB

 

[blueboyz]

Thank you LB. I had used that formula, but the report is designed so they can display a specific time frame (like a week) and the same week last year does not have the same dates as this year (this year: Nov 5-11, 2007 last year: Nov 6-12, 2006).

 

[lbass]

Yes, I agree. So do you have it working properly now?

-LB

 

[blueboyz]

Yes, LB. Thank you. I used the formula that was suggested above. This gets me the correct week except for years where there may be 53 weeks:

@LastYearStartDate
DateAdd("ww", -52, {?startdate})

@LastYearEndDate
DateAdd("ww", -52, {?enddate})

The formulas come up with the correct dates for Last Year, but I am researching the amounts for Last Year. They don't seem to be quite right. Peachtree's database is so different from any other.

 

[lbass]

You probably need to determine what the startdate is for the first week of the year. There is a condition for the datepart function that is based on what the first week of the year should be, e.g., datepart("ww",{table.date}, crSunday, crJan1). Look up the first week of the year constants in the Help section (I don't have access right now).

-LB

 

[goranm]

Hi blueboyz!

If there is a year with 53 weeks (e.g. 1998), what week will you then want to show for the year before?

And have you got the amounts for Last Year to show up correctly yet?

/Goran

 

[blueboyz]

Goran:

I will use your formulas but use "-53" for years that have 53 weeks instead of 52.

I was able to get the amounts to display correctly on the report.

Thank you for your help. It was a life saver.