How might I make a BEx formula with different addends based on Fiscal Period of current quarter?

[Former Member]

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.

[john_hawk]

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

[kohesco]

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

[Loed]

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