Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 01, 2017 at 04:14 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 03, 2017 at 02:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 18, 2017 at 09:31 PM

    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

    Add comment
    10|10000 characters needed characters exceeded