Weektodate comparisons stop working at start of new year 1

[petejigsaw]

thread767-1634738

In the above thread, lbass helped me get comparison reporting working so I could show comparisons of sales in a week number in Year 2010 against the same week number in 2011.

However, now its 2012, the data selection formula below has stopped returning any data for the previous year.....

({%Year} = year(currentdate) or {%Year} = year(currentdate)-1) and
datepart("ww",{peranal.pa_date}) = datepart("ww",currentdate) and
dayofweek({peranal.pa_date}) <= dayofweek(currentdate)

I have tested by removing the datepart and dayofweek criteria and get the expected results.

If I then add back the datepart section, I get the expected results.

Therefore, the problem is with the dayofweek criteria, but I can't work out why???????

Can anybody help?

Thanks in anticipation.

 

[lbass]

Create a separate formula for each component of the formula and place them in the detail section to see what is being interpreted differently. I think the datepart in combination with the SQL expression for year might be at fault--they might have different bases for determining the first week of the year.

-LB

 

[petejigsaw]

Thanks lbass

I have done that, but I'm none the wiser.....

Here's a line from the returned records. Essentially, unless I remove the dayofweek({peranal.pa_date}) <= dayofweek(currentdate) criteria, the report only returns records from 2012.

pa_date = 01/01/12
Year = 2012
dayofweek pa_date = 1
dayofweek currentdate = 3
datepart ww pa_date = 1
datepart ww currentdate = 1
%year = True
%year -1 = False

I can't see anything obvious in the way the date are being interpreted, so really am stuck.......

Any ideas how I can remedy this tricky one?

Thanks

 

[lbass]

Since you are saying it is not returning any dates for the previous year, try removing the non-functioning selection formula and show how these formulas look for last year's data. Please show results for January 1, 2011, and maybe a few days after.

-LB

 

[petejigsaw]

Thanks again lbass.

Right, removing the non-functioning section ( dayofweek({peranal.pa_date}) <= dayofweek(currentdate) )results in some interesting data.

The report then produces data for 1 Jan 2011, then jumps straight to 1 Jan 2012, 2 Jan 2012, 3 Jan 2012 etc.

Here's the additional data for 1 Jan 2011:

pa_date = 01/01/11
Year = 2011
dayofweek pa_date = 7
dayofweek currentdate = 5
datepart ww pa_date = 1
datepart ww currentdate = 1
%year = False
%year -1 = True

The mystery continues, as I can't for the life of me work out what is going on!

 

[lbass]

Well, you can see that in 2011, there were no days of the week less than the dayofweek of the current date for Week 1. Please also show some data for January 2-7 for 2011. Remove ALL record selection criteria first.

-LB

 

[petejigsaw]

Hi lbass

I think this data will help! Please note, dates stated in DD/MM/YY format.

pa_date = 02/01/11
Year = 2011
dayofweek pa_date = 1
dayofweek currentdate = 5
datepart ww pa_date = 2
datepart ww currentdate = 1
%year = False
%year -1 = True

pa_date = 03/01/11
Year = 2011
dayofweek pa_date = 2
dayofweek currentdate = 5
datepart ww pa_date = 2
datepart ww currentdate = 1
%year = False
%year -1 = True

pa_date = 04/01/11
Year = 2011
dayofweek pa_date = 3
dayofweek currentdate = 5
datepart ww pa_date = 2
datepart ww currentdate = 1
%year = False
%year -1 = True

pa_date = 05/01/11
Year = 2011
dayofweek pa_date = 4
dayofweek currentdate = 5
datepart ww pa_date = 2
datepart ww currentdate = 1
%year = False
%year -1 = True

So Week 1 finished on the 1 Jan 2011, on a Sunday, based on the pa_date value, meaning that Week 1 had started in 2010, contrary to the ISO week numbering convention?????

Does this mean through 2011, my report was comparing different week numbers?

And how can I resolve the week number issue for the pa_date value?

Many thanks

 

[lbass]

The report is actually working properly in the sense that for week #1 in 2012, there happened to be seven days, while for week #1 in 2011, there is only one day (Saturday, or dayofweek=7).

If you run the report next week, then the week number values would be the same, and you would get a complete set of data.

You do have the option of setting what counts as the first day of the year and of setting what counts as the first day of the week, and you would need to use both of these in the datepart function, as in:

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

Other options are FirstJan1, FirstFourDays, or the System setting.

See the list of First Week of Year Constants under the Date category in the functions list (inside the formula editor). Or look these up in the Help section to determine which one corresponds to your expectations.

-LB

 

[petejigsaw]

lbass, you're a star!!!!

The ISO week numbering system requires that Week 1 is the first full week in each year starting on a Sunday, so your

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

suggestion worked a treat.

This is the way that UK statistical periods are reported, so we base all our reporting on the same to be consistent.

Man, many thanks - a headache out the way!

Happy New Year!