Hello, I am trying to make a formula in a BEx query that is the sum of actuals and forecast based on which period of the current quarter we are in (1st, 2nd, or 3rd).
Example:
Current period is 1st period of current quarter: Formula = Forecast_1 + Forecast_2 + Forecast_3
Current period is 2nd period of current quarter: Formula = Actual_1 + Forecast_2 + Forecast_3
Current period is 3rd period of current quarter: Formula = Actual_1 + Actuals_2+ Forecast_3
Seems like it should be straightforward, but I'm still kind of stuck.
Thanks for your help.
Hi,
Try to use the variable 0CQUART under 0CALQQUARTER object. Else, you need to do a CMOD code for your 0FISCPER based on your company's definition of PERIOD.
Regards,
Loed
Hi
I guess, you will need something like
(current period == 1)* formula_1 + (current period ==2)*formula_2 + (current period==3)*formula_3 + (current period == 4)*formula_1 + (current period ==5)* formula_2 + (current period ==6)*formula_3 + etc etc
Grtz
Koen
Consider using Boelean logic in formulas or calculated key figures:
Quarterly total =
[(Current period is 1st period of current quarter) * (Forecast_1 + Forecast_2 + Forecast_3)] +
[(Current period is 2nd period of current quarter) * (Actual_1 + Forecast_2 + Forecast_3) ] +
{(Current period is 3rd period of current quarter) * (Actual_1 + Actuals_2+ Forecast_3)]
When one statement is true ( equals 1 ) then the other statements are false ( equals 0) , leaving only the the correct formula.
Let us know your final solution.
John Hawk