Compare this weektodate with the same week last year

[petejigsaw]

Hi

I am working on redesigning a sales report so that in addition to the current weektodate sales, it shows alongside the same week numbers sales from last year.

I'm sure I could do this by formulas, but I have experimented with the datepart function, so as to get week numbers etc, but my stumbling block is that the report runs automatically, without parameter input, on a Saturday evening (our weeks run Sunday-Saturday - standard UK statistical period).

I want the data to look like this:

Weekly Sales - week number XX

Sales in week Sales in week l/year
Store A XXXXX XXXXXXX
Store B XXXXX XXXXXXX
Store C XXXXX XXXXXXX

Total XXXXX XXXXXXX

If I can get this working, I can then extend it to show monthtodate figures on the same basis.

Can anyone point me in the right direction?

Thanks

 

[lbass]

You should be able to use a record selection formula like this:

(
year({table.date}) = year(currentdate) or
year({table.date}) = year(currentdate)-1
) and
datepart("ww",{table.date}) = datepart("ww",currentdate)

This assumes that by Saturday evening you mean sometime before midnight.

This would be more efficient if you used a SQL expression for the year function.

-LB

 

[petejigsaw]

Thanks lbass

I've done that and it works fine for the report run at the end of the week (i.e. Saturday evening).

However, is I wanted to run it daily, as a 'week to date' report appended to the bottom of a daily sales summary, how can I restrict the previous year to only include the same number of days as have elapsed so far this year 'week to date'?

 

[lbass]

Add a line to the previous:

and
dayofweek({table.date}) <= dayofweek(currentdate)

-LB

 

[petejigsaw]

lbass, star as usual!

That works fine and the principle is very useful for other reports I'm working on.

One final question - is it possible to restrict the previous year's 'month to date' to include only the number of days that have elapsed in the month using a similar basis?

Many thanks

 

[petejigsaw]

I think I may have worked it out. I added:

and
datepart("d",{table.date}) <= datepart("d",currentdate)

Just need to check that it's giving accurate results now!

 

[petejigsaw]

Right, now I've got that all working, I've turned to improving performance.

lbass, in your earlier response, you indicated that it would be better to use an SQL expression for the 'Year' function.

As a newbie to using SQL expressions in reporting, I'm struggling to get it to work.

Using Crystal Reports 2008, this is the current SQL query (derived from the selection criteria entered) that Crystal is using:

SELECT peranal.pa_proc, peranal.pa_type, peranal.pa_cost_pr, peranal.pa_sell_pr, peranal.pa_qty, peranal.pa_date, peranal.pa_branch, peranal.pa_order, peranal.pa_vat_det
FROM frs@fairway_live:radii.peranal peranal
WHERE ((peranal.pa_type='01' OR peranal.pa_type='02' OR peranal.pa_type='03' OR peranal.pa_type='04' OR peranal.pa_type='05' OR peranal.pa_type='08' OR peranal.pa_type='09' OR peranal.pa_type='10' OR peranal.pa_type='14' OR peranal.pa_type='54') OR peranal.pa_type='06' AND peranal.pa_proc='RPO')
ORDER BY peranal.pa_branch


All the date filters you pointed to are done once the data is hauled back from the server, so I need some guidance on how to implement an SQL expression for the 'year' function below:

(year({peranal.pa_date}) = year(currentdate) or
year({peranal.pa_date}) = year(currentdate)-1)

Sorry if this is basic, but I've tried a few possible ways without success.

Thanks

 

[lbass]

Sorry, I misplaced this thread. What I meant was to replace each element of the formula with a SQL expression {%year}, as in:

year(`paeranal`.`pa_date`)

Functions are specific to datasources, as is punctuation, but you can add the field from your field list to get the correct punctuation. Check your function list to see if year is on it, and if so, use that. It might appear like this when added:

{fn year(`paeranal`.`pa_date`)}

There should be functions for week and dayofmonth.

Then you would the SQL expressions in the selection formula like this:

(
{%year} = year(currentdate)-1 or
{%year} = year(currentdate)
) and
{%week} = datepart("ww",currentdate) and
{%dayofmonth} <= day(currentdate)

You don't have to use SQL expressions for the currentdate part of the formula, because the result is always a constant, and will pass to the SQL without a SQL expression. I tested this and the entire statement passed to the SQL.

A note though on the week function--I found that this function does not necessarily return the same weeknumber for different databases (probably because of differences in what is considered the first week of the year by default), so be careful if you are using multiple datasources in your report, e.g., with a subreport.

-LB

 

[petejigsaw]

I'm now looking to add a YTD section to this report, so that as well as WTD and MTD, it shows the YTD sales figures.

However, we have a year which runs 1st February to 31st January, so I need to make sure the report offsets from a simple YTD.

Can anyone point me in the right direction?

Many thanks