Fiscal Years and how to use in record selection to improve performance

[petejigsaw]

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.

 

[briangriffin]

Do you have the ability to create tables on your database server? If so you can take a streamlined approach.

 

[petejigsaw]

Hi briangriffin

I'm afraid not - I'm reporting against our retail system's main database, so creating additional tables isn't an option.

Thanks for the suggestion though.

 

[briangriffin]

Just to be sure - the table wouldn't have to be in your retail system database, it could be in an existing or newly created database on the same server, or even on a different server. It's worth asking because a calendar table makes all your date based reports a breeze.

If not I'll take a harder look at your current formulas.

 

[petejigsaw]

Ah, I see.

I could create another table in a parallel database, with calendar details in it. So if that's going to help, please point me in the right direction.

However, as this is a simple report with only one table of data in it, I'm keen to keep performance as snappy as possible.

Thanks

 

[briangriffin]

Great, here's a sample script that creates a table variable just so you can see the approach. You would create an actual table, and make sure to set the calendardate field as the primary key.

declare @v_calendartable table(

calendardate smalldatetime,
calendaryear int,
calendarmonth int,
fiscalyear int,
fiscalmonth int)

declare @v_startdate smalldatetime

set @v_startdate = '1/1/2011'

while @v_startdate <= '12/31/2015'

BEGIN
insert into @v_calendartable (calendardate,calendaryear,calendarmonth)
values (@v_startdate, YEAR(@v_startdate), MONTH(@v_startdate))
set @v_startdate = @v_startdate + 1

END

select * from @v_calendartable

After that it's probably easiest to just run a bunch of update statements to populate the other fields, such as

set fiscalyear = calendaryear + 1 where calendarmonth >= 2

(that's not right, but you get the idea)

Once done, you just need to join the calendar table to your other report tables using the calendardate field, and use this in your report logic:

calendartable.fiscalyear = 2013 (or whatever).

This approach has a multitude of uses - my version contains fields for pay period start/end, paydays, etc. You can just add additional columns as the need arises.

 

[xxyyzz49]

This breaks out down into a series of formulas that maybe can be combined.
1. Create a formula to return the current fiscal year, as in the original post

// @CurrentFiscalYear
if MONTH ( {@CurrentDate} ) >= 2
then YEAR ( {@CurrentDate} )
else YEAR ( {@CurrentDate} ) -1;
// @PriorFiscalYear

2. Create a formula to return the prior fiscal year, use 1 and subtract a year

{@CurrentFiscalYear} - 1

3. Create two formulas to return the start date of the current and prior fiscal years

// @CurrentFiscalStart
DATETIME({@CurrentFiscalYear}, 02, 01);
// @PriorFiscalStart
DATETIME({@PriorFiscalYear} , 02, 01)

4. Create a formula to set a global date time variable range for the current fiscal year range.

//@CurrentDateRange
GLOBAL DATETIMEVAR RANGE YTD ;
LOCAL NumberVar curMonth;

curMonth = month({@CurrentDate});

If curMonth < 2
then YTD := {@CurrentFiscalStart} to {@CurrentDate}
else YTD := {@PriorFiscalStart} to dateadd("YYYY",-1,{@CurrentDate});
true;
//@PriorDateRange
GLOBAL DATETIMEVAR RANGE PYTD ;
LOCAL NumberVar curMonth;

curMonth = month({@CurrentDate});

If curMonth < 2
then PYTD := dateadd("YYYY",-1,{@CurrentFiscalStart}) to dateadd("YYYY",-1,{@CurrentDate})
else PYTD := dateadd("YYYY",-1,{@PriorFiscalStart}) to dateadd("YYYY",-2,{@CurrentDate});
true;

Now you can use the @PriorDateRange and @CurrentDateRange formulas to select records

 

[IanWaterman]

If its a single table I would use a command and add extra columns that replicate your current and fiscal year.

Not sure which database you are using but most accept a case statement your command would look something like

Select t.*,
case when Month( {peranal.pa_date} ) >= 2 then Year ( {peranal.pa_date} ) + 1 else Year ( {peranal.pa_date}) end fiscalYear,
case when Month ( CurrentDate ) >= 2 then Year ( CurrentDate ) + 1 else Year ( CurrentDate ) end CurrentfiscalYear
from your table t
where
case when Month( {peranal.pa_date} ) >= 2 then Year ( {peranal.pa_date} ) + 1 else Year ( {peranal.pa_date}) end = 2013

This will work for SQL server

Ian

 

[petejigsaw]

Thanks pmsawyer

I've created all the formulas you outlined, and understand how they all link together.

However, I'm stuck at the stage of how to use @CurrentDateRange and @PriorDateRange in record selection.

If I try to pull records where {peranal.pa_date} is equal to @CurrentDateRange or @PriorDateRange, I get a warning that they are not dates, but I can't figure out how else to use them to pull the records I need.....

Can you point me in the right direction?

Thanks

Peter

 

[petejigsaw]

And just to clarify, {peranal.pa_date} is a DATE field, rather than a DATETIME field, which I think may be the issue?????

 

[xxyyzz49]

The missing piece is to create a selection formula the will use the GLOBAL DATETIME RANGE variables YTD and PYTD.

Select a year in the current fiscal year

GLOBAL DATETIMEVAR RANGE YTD;
{Sheet1_.F3} in YTD

Select a record in the prior fiscal year

GLOBAL DATETIMEVAR RANGE PYTD;
{Sheet1_.F3} in PYTD

Sheet1_F3 is a date time column in a spread sheet.

Also be sure to get the proper values in YTD and PYTD by placing the following in the report header if not done already:
@CurrentFiscalStart
@PriorFiscalStart
@CurrentFiscalYear
@PriorFiscalYear