Compare weeks date formula 2

[hencyt]

I need a report that will compare sales totals this week with sales totals this week last year. What is the best way to get this? I was thinking I could use the datediff formula something like:

if DateDiff(????,CurrentDate,{ARN_InvoiceHistoryHeader.InvoiceDate}) =1 then
{@Invoice total}
else
0

But I don't really know how to get it right. I know I would need at least 2 formulas like this-one for this week and one for this week last year. Then I would need a similar one for last week and last week last year and so on. If it is not too complicated it would be nice for it to change with the current date- i.e the current weeks totals always displayed at the top of the report.

The result report would be something like:

Sales Totals '03 Totals '02
Week 49 5555.23 4525.15
Week 50 1256.45 556.22
Week 51 10.02 54.21
Week 52
Week 1 ('04 vs '03)

Will the function "WW" do the trick?

Thanks in advance,
Sunny

 

[synapsevampire]

Create a record selection formula akin to:

datepart("ww",{mytable.somedate1}) = datepart("ww",currentdate)
and
year({mytable.somedate1}) >= year(currentdate)

Now you only have those two weeks worth of data in the report.

You can now base a Crosstab on the date (group option set to year), or use Running Totals or formulas based on:

Year(currentdate)

or

Year(currentdate)-1

-k

 

[synapsevampire]

Ooops, sorry, I didn't see that you were using multiple weeks.

Same theory applies though, create a record selection to pull just the 2 years )omit the week part)

Then create a group by the datepart("ww",{table.date})

Then use Running Totals (In the evaluate use a formula) or Formulas (use an if to check year) and explicitly state year() or year()-1:

-k

 

[hencyt]

thanks SV- I am working it out now.

Sunny

 

[hencyt]

Ok the report is done and works great. One question- anyone have any ideas why I have 53 weeks? How does Crystal calculate the ww function?

Thanks,
Sunny

 

[lbass]

This function assumes as a default that you want to count the week that contains January 1 as Week 1. Since this doesn't necessarily land on a Sunday (the default start for the week), there can be 53 weeks. The datepart function allows you to specify the day of the week you want as the week start, and also what week you want to count as the first week, as in:

datepart("ww",{table.date}, crSunday, crFirstFullWeek)

-LB

 

[hencyt]

you are awesome! I figured it had to be something like that.

Thanks-
Sunny

 

[crystalvictim]

lbass,

we had a similar problem a couple of weeks ago, but we haven't been able to solve it with datepart.

Located in Germany, we first tried to use datepart with crMonday instead of crSunday and replaced crFirstFullWeek with crFirstFourDays.

So we expected a date within the range from 2003/12/22 to 2003/12/27 to return 52 and a date within 2003/12/28 to 2004/01/04 to return 1.

Astonishingly 2003/12/31 returned week number 53 (which does not exists in 2003). The problem hast been reproducable with both CR 8.5 and 9.

So we contacted the support team: From their point of view, everything's fine as the function works like some similar fomula in MS Excel (whatever relation there ist between the two of them - probably using the same sourceode? ;-)). I haven't been able to figure out a way to tell the excel formula about things like 'crFirstFourDays' but that's another story...
Anyway, the crFirstFourDays does not seem to have any significant effect on datepart.

Crystal suggested a workaround using datediff but as it seems to me now, this isn't always a valid alternative.

So if other developers are expierencing the same problem, I'll post two possible workarounds to either deal with or avoid this matter.
But perhaps there's something we've just been overlooking so far.

Regards,
Frank

 

[synapsevampire]

There are different ways that work weeks are calculated, and SQL Server does it differently from Oracle (Oracle using the ISO standard, which it sounds like you would prefer).

I've coded it out in SQL Server and Crystal before, but the a better long term solution for dealing with dates is to build a Periods Table which houses attributes for every day (can be more granular), such as Week, Month, Quarter, Year, Holiday, Weekday, Fiscal week, Fiscal Month, etc.

I have a FAQ here on this topic which includes SQL (SQL Server) for creating one:

faq767-4532

In your case you can join your date field to this lookup table

-k

 

[crystalvictim]

Hardcoding it in crystal was our first solution ;-)
But as the reports concerned are based on oracle, we found out sql expression fields being quite a neat way to deal with the issue.
The formula is something like: to_char("TABLE_NAME"."FIELD_NAME",'IYYY/IW')
which did indeed result in returning the correct values.
You just have to keep your eye on using the native oracle driver offered by crystal instead of an odbc connection.

 

[lbass]

crystalvictim,

I think the datepart function works as intended. For 2003, where January 1 was a Wednesday, if you use:

datepart("ww",Date(2003,12,31),crMonday, crFirstFourDays)

...the result will correctly be 53. The formula counts the Monday of the first week containing four days in January as Week 1, and since there are five days in the week starting Monday, December 30, 2002, through Sunday, January 5 (January 1 - 5), it meets the criteria to be counted as week 1. The week starting Monday, December 29, thus becomes week 53. If you test the formula by substituting day 28 or 29, you will see that the count changes to 53 on December 29.

The formula ordinarly evaluates for the year the date is in. If you change the formula to:

datepart("ww",Date(2004,01,01),crMonday, crFirstFourDays)

The result will be 1, since it is week 1 of 2004, using these criteria, even though it is in the same week as 12/31/03, which evaluates to 53.

You can see that using crFirstFourDays does have an effect by changing the first day of the week in the formula. The following:

datepart("ww",Date(2003,12,31),crThursday, crFirstFourDays)

...results in 52, because by using Thursday as the beginning day of the week, there are no longer four days in the first week of 2003 to meet the criteria. The first week, from Thursday, December 26, 2002, to January 1, 2003, contains only 1 day in January, so January 2 becomes the first day of the first week.

The only "funny" result arises for the first week that doesn't "count" as the first week:

datepart("ww",Date(2003,01,01),crThursday, crFirstFourDays)

...would result in 52, which doesn't make sense as its weeknumber for 2003. A more accurate result would be 0--since it is being excluded from the current year. Theoretically, if you are using a weekcount, you might be excluding records outside of the acceptable range so this would not become a factor.

-LB

 

[crystalvictim]

lbass,

I'm sorry, but now I'm REALLY confused :-((
I agree with Dec. 30 through Jan 5 (2002) being in week 1. But Dec. 29 should have been week 52 and not 53 (according to our calendars).

I've tried the following formula with the 2002's dates you mentioned:

datepart('ww',date(2002,12,31),crMonday,crFirstFourDays)

The result was 53 which does not meet your prediction ;-).
For 2003, it's the same (wrong) result.

In Germany, our calendar (2003) looks as follows:
2003/12/22 - 2003/12/28: week 52
2003/12/29 - 2004/01/04: week 1
(week starts on Monday).

So what I'd expect the formula to return if using it on any date between 2003/12/29 and 2004/01/04 would be 1.
In fact it shows 53 for all dates in this range except 2004/01/04. From this date on, it seems to work as I expect.

Either I don't get the point (that's quite probable ;-)) or there's something wrong with the function...

Regards,
Frank

 

[lbass]

I think you and I were talking about different years. I was not suggesting that you test 2002 dates--I was talking about 2003 dates. Let me clarify by adding in the years here:

"I think the datepart function works as intended. For 2003, where January 1, 2003 was a Wednesday, if you use:

datepart("ww",Date(2003,12,31),crMonday, crFirstFourDays)

...the result will correctly be 53. The formula counts the Monday of the first week containing four days in January as Week 1, and since there are five days in the week starting Monday, December 30, 2002, through Sunday, January 5, 2003 (January 1 - 5), it meets the criteria to be counted as week 1. The week starting Monday, December 29, 2003 thus becomes week 53. If you test the formula by substituting day 28 or 29, you will see that the count changes to 53 on December 29, 2003.

The formula ordinarly evaluates for the year the date is in. If you change the formula to:

datepart("ww",Date(2004,01,01),crMonday, crFirstFourDays)

The result will be 1, since it is week 1 of 2004, using these criteria, even though it is in the same week as 12/31/03, which correctly evaluates to 53.

You can see that using crFirstFourDays does have an effect by changing the first day of the week in the formula. The following:

datepart("ww",Date(2003,12,31),crThursday, crFirstFourDays)

...results in 52, because by using Thursday as the beginning day of the week, there are no longer four days in the first week of 2003 to meet the criteria. The first week, from Thursday, December 26, 2002, to January 1, 2003, contains only 1 day in January 2003, so January 2, 2003 becomes the first day of the first week.

The only "funny" result arises for the first week that doesn't "count" as the first week:

datepart("ww",Date(2003,01,01),crThursday, crFirstFourDays)

...would result in 52, which doesn't make sense as its weeknumber for 2003. A more accurate result would be 0--since it is being excluded from the current year. Theoretically, if you are using a weekcount, you might be excluding records outside of the acceptable range so this would not become a factor."

Let me also respond to your statements:

"In Germany, our calendar (2003) looks as follows:
2003/12/22 - 2003/12/28: week 52
2003/12/29 - 2004/01/04: week 1
(week starts on Monday).

So what I'd expect the formula to return if using it on any date between 2003/12/29 and 2004/01/04 would be 1.
In fact it shows 53 for all dates in this range except 2004/01/04. From this date on, it seems to work as I expect."

Datepart("ww",Date(2003,12,22), crMonday, crFirstFourDays) correctly evaluates to week 52, as it does if you substitute 12/28/2003.

Datepart("ww",Date(2003,12,29), crMonday, crFirstFourDays) correctly evaluates to 53, since it is evaluating the weeknumber in relation to the year of the date (2003), i.e., 12/29/2003 is week 53 of year 2003, which starts on Monday, 12/30/2002.

If you then use Datepart("ww",Date(2004,01,01), crMonday, crFirstFourDays), it will correctly evaluate to week 1, since it evaluating based on the year 2004.

I think the issue is that you want the function to operate differently than it does. This does not mean it is operating incorrectly. I don't think we know enough about your issue to develop a solution, so maybe you should provide more information. One possibility might be to always evaluate the weeknumber based on the weekending date, rather than for each day of the week. You could adjust each date to reflect the weekending date by using a formula like the following {@sunwkending}, which assumes Sunday is the end of the week:

{table.date} - dayofweek({table.date},crMonday) + 7

Then you could use this in your week number formula instead of {table.date}:

datepart("ww",{@sunwkending},crMonday, crFirstFourDays)

I think this would give you the weeknumbers you might be looking for.

-LB

 

[crystalvictim]

Now I seem to get the point... Finally ;-)

I understand that datepart doesn't seem to be designed for our purpose. At least not directly - so I'll agree with you on this not being a bug.
We actually solved the problem by either using an sql expression, by handling this issue directly on the db side or by hardcoding the set of rules for determining the week number.
But I have to admit that the solution you suggested might indeed by more elegant

Thanks for your help and patience.