Date formula

[veles]

Hi,
I am working on a report that involvels dates and I would appreciate some help.
I am using crystal XI.

Our clients are registered with a start and end date. Some clients are registered for one year, some for 2, 3 and 4 years. Each client has a start and end date. We use fiscal years from April 1sr to March 31st the next year.

I need to create a formula that will give me registered clients in each fiscal year.

Example:
Client 1 - Start_Date April 1 2010 - End_Date March 31 2011
Client 2 - Start_Date April 1 2010 - End_Date March 31 2012
Client 3 - Start_Date April 1 2011 - End_Date March 31 2013
Client 4 - Start_Date April 1 2010 - End_Date March 31 2014

I am using the Start_Date to create a fomula for fiscal year. This part is working fine.

My issue is creating a formula showing clients in each fiscal year.
Client 1 needs to show in fiscal 10/11
Client 2 in fiscal 10/11 and fiscal 11/12
Client 3 in fiscal 11/12 and 12/13
Client 4 in fiscal 10/11, 11/12, 12/13 and 13/14

So in:
Fiscal 10/11 in need to show Client 1, 2 and 4
Fiscal 11/12 - Client 2, 3 and 4
Fiscal 12/13 - Client 3 and 4
Fiscal 13/14 - Client 4

I hope this makes sence.

Thanks in advance

 

[hilfy]

You could do this with a formula, but the years would have to be hardcoded into it, so it would have to continually be updated and extended. Do you have the option to add a new table to your database? If so, here's what I suggest:

Add a table called something like "Fiscal_Year". The structure would be something like this:

Start_date: Date
End_Date: Date
FY: String

Start date would be the 4/1 date for the year, end date would be the 3/31 date, and FY would be '10/11', '11/12', etc.

Join to this table with this logic:

client.start_date <= fiscal_year.start_date and client.end_date >= fiscal_year.end_date

You can then group on Fiscal Year and show the clients that are valid for that year.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[veles]

Hi Dell,

I can't add a table in the DB.
If you can explain how to do this with hardcoding the years in a formula I would really appreciate it. I can update the code for each year as needed in the future.



Thanks

 

[hilfy]

Thinking about this further, you may not be able to do it with just a formula - especially since it looks like you want to group by fiscal year.

Since you can't create a table, there may be a way to simulate a table. Can you create a view in the database? If not, you could possibly use a command. How are your SQL skills? What type of database are you using? How large is the data set you're working with?

The simple version of the command would look something like this (I'm using Oracle syntax, if you're using a different database, you'll need to change the date conversions as appropriate):

Select
  '10/11' FISCAL_YEAR,
  To_Date('01-APR-2010') START_DATE,
  To_Date('31-MAR-2011') END_DATE
from dual
UNION ALL
Select
  '11/12' FISCAL_YEAR,
  To_Date('01-APR-2011') START_DATE,
  To_Date('31-MAR-2012') END_DATE
UNION ALL ... <until the last year you want to track>

If your data set is not very large, you could create just this as a command and then join it to the existing tables in the report, grouping on {command.FISCAL_YEAR}.

However, if your dataset is larger (more than a couple-thousand records), this will cause your report to be very slow because Crystal will pull ALL of the data into memory and do the join, filter, group, and sort in memory instead of pushing it to the database. In that case, you'll want to convert your report to use a single command to pull ALL of the data for the report, using something like the query above in a sub-select.

I can also see a possible way to do this using a separate sub-report for each fiscal year instead of the command, but that would be very slow as well as being difficult to maintain. Each sub-report would run it's own query to pull the client details, which would cause a lot of basically duplicate work in the database.

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.

 

[veles]

Thanks for the reply.

The DB is Oracle. I tried doing this with formaulas. I created a formula for each start date and end date getting them to show me true or false. This seems to work (I didn't have a lot of time to work on this today)
I started naming the formulas 11/12 startdate and 11/12 enddate with code sratdate is in 2011-04-01 00:00:00 to 2012-03-31 23:59:59 and so on. This is giving me true. I was going to do a simmilar one for end date. Then a third one for FY11/12 startdate=true and enddate=true for the said fiscal year. I was hoping to do a fourth formula to have if FY11/12=ture then FiscalYear11/12 and so on and group by this fourth formula.

I will try the command option to se what the results are.
Just for clarification is the To_Date the field or the START_DATE.

Thanks

 

[hilfy]

START_DATE is the name of the field when the query is run. The contents are To_Date of the date that you use.

Formulas aren't going to let you group your data by FY - you need to have a separate record for each FY in order to be able to do that.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Charliy]

if month({datefield}) < 4 then totext(year({datefield}),'####') else totext(year({datefield)+1,'####') ;