# BEx Query: Need to create year range within calculated KF

Hello dear community,

Thanks a lot!!

10|10000 characters needed characters exceeded

Posted on Mar 10, 2017 at 03:02 PM

Hi Chris,

Your stated approach of "summation of Sub KFs for actuals, target and plan, each multiplied with Boolean Statements comparing the column year to current year" is in fact the most effective one and will work. In answer to your problem "I need to find a way how to employ the column years within the Booleans", you can achieve this by creating two Formula Variables, one for the Selection Variable and one for characteristic 0FISCYEAR. This is achieved using Replacement Path Variables.

Here is a working example that you can adapt to your scenario. I have used Calendar Year (0CALYEAR) instead of Fiscal Year (0FISCYEAR) but the principle is the same.

BEx Query Structure:

BEx Query Filter with Offset Variables:

The above example uses offset variables to include the selected year and year before. However, the approach will also work the same for your case of an offset range from "- 6 to + 4 years".

Calculated Key Figure With Boolean Formula:

The above calculation shows how to include the Formula Variables and boolean logic. You can adapt this for the logic required in your scenario.

Formula Variable Definition for BEx Calendar Year Variable:

Formula Variable Definition for Calendar Year Characteristic:

Result in BEx Analyzer:

As you can see above, the Calculated Key Figure "Measure" shows the desired Sub Key Figure based on the Year. In my example, if the Year < Selected Year then Taxi Out Time is substituted whereas if the Year >= Selected Year then Taxi In Time is substituted. In my example, 2014 is the Selected Year.

Regards,

Mustafa.

10|10000 characters needed characters exceeded
• Posted on Mar 02, 2017 at 09:38 AM

Hello,

can you attach bex query designer settings?

regards,

anand

10|10000 characters needed characters exceeded
• Posted on Mar 02, 2017 at 10:42 AM

Hi,

which are the infoobject you assign to rows and columns. Just attach the screenshot from rows and columns.

Regards,

Anand

10|10000 characters needed characters exceeded
• Posted on Mar 02, 2017 at 01:14 PM

Hi,

Replacement path Variable will not work on offset which are set in filter.

if i understood your problem correctly then I think there are two solutions :

1) Create eleven customer exit variable that will calculate value from input variable then use this eleven customer exit variable in Boolean.

2) Create 66 restricted key figures and use this RKF's in your Boolean.

Regards,

Anand

10|10000 characters needed characters exceeded
• Hi Anand,

A Replacement Path Variable will in fact work with offsets set in the filter. Please see my example below.

Regards,

Mustafa.

• Posted on Mar 02, 2017 at 01:43 PM

Hello Anand,

thanks very much for your advice. That was very helpful. OK, I will have to check which solution to go with.

Best regards.

10|10000 characters needed characters exceeded
• Posted on Mar 02, 2017 at 04:43 PM
10|10000 characters needed characters exceeded
• Posted on Mar 06, 2017 at 06:39 AM

HI,

If I understand your scenario correctly then this is my suggestion..

I think you have 3 KFs: KF for annual target, KF for actuals, and KF for plan values? Is that correct?

If yes then you may create 11 SELECTIONS..

SELECTION_1 = fiscal year (filter with ZBCFHFISC_P1X_001 offset -6) and KF is KF_ACTUALS

SELECTION_2 = fiscal year (filter with ZBCFHFISC_P1X_001 offset -5) and KF is KF_ACTUALS

SELECTION_3 = fiscal year (filter with ZBCFHFISC_P1X_001 offset -4) and KF is KF_ACTUALS

SELECTION_4 = fiscal year (filter with ZBCFHFISC_P1X_001 offset -3) and KF is KF_ACTUALS

SELECTION_5 = fiscal year (filter with ZBCFHFISC_P1X_001 offset -2) and KF is KF_ACTUALS

SELECTION_6 = fiscal year (filter with ZBCFHFISC_P1X_001 offset -1) and KF is KF_ACTUALS

SELECTION_7 = fiscal year (filter with ZBCFHFISC_P1X_001 offset 0) and KF is KF_ANNUAL_TARGET

SELECTION_8 = fiscal year (filter with ZBCFHFISC_P1X_001 offset 1) and KF is KF_PLAN_VALUES

SELECTION_9 = fiscal year (filter with ZBCFHFISC_P1X_001 offset 2) and KF is KF_PLAN_VALUES

SELECTION_10 = fiscal year (filter with ZBCFHFISC_P1X_001 offset 3) and KF is KF_PLAN_VALUES

SELECTION_11 = fiscal year (filter with ZBCFHFISC_P1X_001 offset 4) and KF is KF_PLAN_VALUES

Just post here for any questions..

Regards,

Loed