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

Thanks
HemlockPrintersAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
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);

budget

Open in new window

0
 
plusone3055Commented:
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
0
 
HemlockPrintersAuthor Commented:
thanks,
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
.....
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mlmccCommented:
Will the start and end dates always be the 1st or last day of the month?

mlmcc
0
 
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.
0
 
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?
thanks
0
 
James0628Commented:
> 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)?

 James
0
 
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?

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

mlmcc
0
 
James0628Commented:
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.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.