YTD and Prior YTD reporting - how to set date selection??????

[petejigsaw]

Hi

I'm working on a simple sales report showing sales in the YTD period vs the Prior YTD (PYTD).

I am using Ken Hamady's fiscal year formulas to calculate whether a date falls into the YTD or PYTD period, where the fiscal year starts February 1st each year, as follows:

{@Fiscal Year}
if Month ( {peranal.pa_date} ) >= 2
then Year ( {peranal.pa_date} ) + 1
else Year ( {peranal.pa_date} )

{@Current Fiscal Year}
if Month ( CurrentDate ) >= 2
then Year ( CurrentDate ) + 1
else Year ( CurrentDate )

I have then used these in formulas at detail level to get the sales figures into YTD and PYTD.

If I then run the report with the date selection set simply for a preset period (here 1st Feb 2010 to 16th March 2011 as follows) the report shows the correct output:

{peranal.pa_date} in Date (2010, 02, 01) to Date (2011, 03, 16)

However, I need to code the record selection so there is no date inputting required, so when the report runs, it simply retrieves the data required to show YTD and PYTD. I have tried using the following date selection formula but it returns incorrect results:

(year({peranal.pa_date}) = year(currentdate) or year({peranal.pa_date}) = year(currentdate)-1) and
datepart("d",{peranal.pa_date}) <= datepart("d",currentdate)

I have also tried tweaking it to use the fiscal year formulas as below, but without success

(year({peranal.pa_date}) = {@Current Fiscal Year} or year({peranal.pa_date}) = {@Current Fiscal Year}-1) and
datepart("d",{peranal.pa_date}) <= datepart("d",currentdate)

Can anybody point me in the right direction?

Crystal does not have a command for DAYOFYEAR either, which would have been simpler to use in the date selection formula or an SQL expression to speed report performance.

 

[briangriffin]

Sorry I don't have time to dissect your entire post today, but if a dayofyear function would make things easier for you then compile a custom function:

// DayOfYear function

Function  (dateVar v1)

local numbervar x;
x := v1 - Date(Dateadd ('d',1,v1 - (datepart("y",v1)))) + 1;
x

So:

DayOfYear(currentdate) = 75
DayOfYear(date(2008,12,31)) = 366