Link to home
Start Free TrialLog in
Avatar of HemlockPrinters
HemlockPrinters

asked on

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
Avatar of plusone3055
plusone3055
Flag of United States of America image

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
Avatar of HemlockPrinters
HemlockPrinters

ASKER

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
.....
Avatar of Mike McCracken
Will the start and end dates always be the 1st or last day of the month?

mlmcc
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.
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
> 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
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,
I believe you can specify a minimum and maximum value for the parameter.  That may limit what is entered to valid ranges.

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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.

 James