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
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
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
.....
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?
mlmcc
mlmcc
ASKER
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.
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.
ASKER
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
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
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
ASKER
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,
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
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
budget = Q1/3*2 + Q2/3
else if startdate >= 2014.02.01 And enddate =< 2014.05-31 Then
budget = Q3 + Q4/3
End If