crystal report formula

I need a formula to calculate budget

there are four fiscal quarter fields Q1, Q2, Q3, Q4 in the table
Q1:  Aug 2013 -- Oct 2013
Q2:  Nov 2013 -- Jan 2014
Q3:  Feb 2014 -- Apr 2014
Q4:  May 2014 -- Jul 2014

there are two parameters, startdate and enddate which are SMALLDATETIME type.

if the time frame is from 2013.09.01 --2013.11.31, then budget is Q1/3*2 + Q2/3
2014.02.01----2014.05-31 : Q3 + Q4/3

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If startdate >= 2013.09.01  And endate =< 2013.11.31 Then
budget = Q1/3*2 + Q2/3
else if startdate >= 2014.02.01 And enddate =< 2014.05-31 Then
budget = Q3 + Q4/3
End If
HemlockPrintersAuthor Commented:
those are two examples, I need to calculate the budge in any time frame in this fiscal year.
ie, it could be from 2013.10.01 --2013.12.31
or 2014.01.01-2014.05.31
Will the start and end dates always be the 1st or last day of the month?

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

HemlockPrintersAuthor Commented:
yes, it is always calculated by monthly.
even start date is in the middle of the month, it will be treated as 1st of the month
same as end date will always be last day of the month.
HemlockPrintersAuthor Commented:
If I break Quarter data into monthly data , ie:
Q1 --> Aug, Sep, Oct
Q2 --> Nov, Sep, Jan
so there are 12 months data instead of four quarter data in the table,
it could be a bit easier I think,
how do I calculate the budget?
> so there are 12 months data instead of four quarter data in the table,

 Are you going to actually change the data in the table, so that there is a separate budget field for each month, instead of just one for each quarter?  Or are you just saying that it might be easier to think of the quarters in terms of months?

 I assume that the startdate and enddate parameters will always be in the same fiscal year?  For example, you won't have a date range from 06/01 to 08/31?

 Is there a year field in the table (the year when the fiscal year begins or ends)?

HemlockPrintersAuthor Commented:
yes, I can actually change the data in the table, so that there is a separate budget field for each month.

there is no year field in the table. but I can add this field into the table.

there is only one fiscal year budget in the table, the fiscal year is from Aug 1  2013 to July 1, 2014.

startdate and enddate parameters will always be in the same fiscal year. but the user may input the wrong date,  such as 06/01 to 08/31,  Is ther any 3. Data Validation function available in Crystal report?

I believe you can specify a minimum and maximum value for the parameter.  That may limit what is entered to valid ranges.

FWIW, I asked about the year because I was thinking about using it to "sort" the months, but I ended up not using it.

 Assuming that you can't control the parameter values somehow to make sure that they're valid, I would probably add tests to the record selection formula, so that if the parameters aren't valid, the report just doesn't include any records.  You could also do something like add a message to the report in a text field that is suppressed when the parameters are valid.  Or, if there are different conditions that you want to check for (beginning date greater than the ending date, dates cross the fiscal year boundary, dates in the future, etc.) and you want the report to show a different message for each condition, you could use one or more formulas that check the parameters and produce the appropriate message(s), or nothing if the parameters are OK.

 I've tested the formula below and it seems to work.

 It does not try to handle an invalid date range in the parameters (eg. crossing the fiscal year boundary).  You could add checks on the parameters to the formula and have it just produce 0 if they're not OK.

 The formula uses the original Q1 - Q4 fields that you mentioned.  Change the field names to your actual field names.  If you decide to use monthly budget fields instead, it could be changed to use those.

 f_months is used to associate each calendar month with a fiscal month.  For example, August (month 8) is the first month in the fiscal year, so it gets a 1.  So then we can loop from the starting fiscal month to the ending fiscal month (start_f_month to end_f_month), and add the appropriate budget figures from Q1 - Q4 to the total budget.

Local NumberVar Array f_months := [ 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5 ];
Local NumberVar start_f_month;
Local NumberVar end_f_month;
Local NumberVar fm;
Local NumberVar budget;

start_f_month := f_months [ Month ({?startdate}) ];
end_f_month := f_months [ Month ({?enddate}) ];

for fm := start_f_month to end_f_month do
  select fm
   case 1, 2, 3 :
    budget := budget + ({Q1} / 3)
   case 4, 5, 6 :
    budget := budget + ({Q2} / 3)
   case 7, 8, 9 :
    budget := budget + ({Q3} / 3)
   case 10, 11, 12 :
    budget := budget + ({Q4} / 3);


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Now that I think about it, you don't really need the start_f_month and end_f_month variables.  You could just use f_months [ Month ({?startdate}) ] and f_months [ Month ({?enddate}) ] in the For loop instead.  That was just the way that the formula evolved.

 There's no harm is using those two variables.  I just realized that they're not really necessary.  They might be useful if you were going to do anything else with those values, like test them to make sure that start_f_month was not > end_f_month.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.