Skip to Content
0

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

Mar 31, 2017 at 07:51 PM

64

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Loed Despuig Apr 01, 2017 at 04:14 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Koen Hesters Apr 03, 2017 at 02:56 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
John Hawk Apr 18, 2017 at 09:31 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded