Date parameter for YTD but not calendar year

[kdavis6416]

I am looking to create a Date parameter to dispay the YTD to be July 2011 to current.. Our Calendar year here is Jul - Jun.. not Jan to Dec.. So I cannot use the standard YTD function.. make sense...

 

[briangriffin]

Since this is something you'll probably be doing in a lot of reports, you may want to create a custom function. Here's one way - go to the Formula Workshop and find Report Custom Functions in the listing on the left. Right click --> New. Give it a name (FiscalYear) and click on Use Editor. Paste this in, and change the name of the date field being evaluated.

Function  (datevar v_inputdate)
local datevar v_fiscalstart;
local datevar v_fiscalend;


if month(currentdate) in [1 to 6]
then 
v_fiscalstart := date(year(currentdate) - 1,7,1)
else
v_fiscalstart := date(year(currentdate),7,1);

if month(currentdate) in [1 to 6]
then 
v_fiscalend := date(year(currentdate),6,30)
else
v_fiscalend := date(year(currentdate) + 1,6,30);

if v_inputdate in v_fiscalstart to v_fiscalend then true else false;

Then your select statement becomes:

FiscalYear(yourtable.yourdatefield) = true

If you don't have Business Objects Enterprice, Crystal Server, Edge, etc. then the function will only reside in this report. You can copy and paste it into new reports from this report, or sometimes it makes sense to create one report that is used only to hold custom functions.

You say you want to use this in a parameter - what would the other choices be? Current Fiscal Year, Prior Fiscal Year? If so you need another function, PriorFiscalYear, and just tweak the code to move the dates back one year.

 

[lbass]

Or you could use a formula like this:

year({table.date}+184) = {?FiscalYear} and
{table.date} <= currentdate

...where {?FiscalYear} is a number parameter.

-LB