petejigsaw
MIS
Hi
I'm still, after almost a year, working on a simple sales report showing sales in the YTD period vs the Prior YTD (PYTD). Our year end is 31 January each year.
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 (2012, 02, 01) to Date (2013, 01, 13)
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.
I'm using an SQL expression on %YEAR to try to improve performance, as below:
{fn YEAR( peranal . pa_date )}
I'm currently using it is the record selection as follows, but I just can't get it to work. I think I'm not using the correct method to utilise the fiscal year, but can't figure out why.
({%Year} = year({peranal.pa_date}) or
{%Year} = year({peranal.pa_date}-1))
Can anyone help??????
Thanks in anticipation.
I'm still, after almost a year, working on a simple sales report showing sales in the YTD period vs the Prior YTD (PYTD). Our year end is 31 January each year.
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 (2012, 02, 01) to Date (2013, 01, 13)
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.
I'm using an SQL expression on %YEAR to try to improve performance, as below:
{fn YEAR( peranal . pa_date )}
I'm currently using it is the record selection as follows, but I just can't get it to work. I think I'm not using the correct method to utilise the fiscal year, but can't figure out why.
({%Year} = year({peranal.pa_date}) or
{%Year} = year({peranal.pa_date}-1))
Can anyone help??????
Thanks in anticipation.