Skip to Content
0

BEx Query: Need to create year range within calculated KF

Mar 02, 2017 at 09:14 AM

311

avatar image

Hello dear community,

my query starts with selection of fiscal year (0FISCYEAR). By Offset, the selection variable creates time range of - 6 to + 4 years at runtime (11 columns).

I need to display annual target for the current year, actuals for years before and plan values for those after. While as mentioned, query is started on any given fiscal year.

I have created one calculated KF to populate values for all columns. It' s a summation of Sub KFs for actuals, target and plan, each multiplied with Boolean Statements comparing the column year to current year.

Depending on where the current year "sits" within the range, the query needs to understand for each column if its year is earlier, equal to or later than the current year, thus set the Boolean Statement of the correct Sub KF to 1 and all the others to 0.

I need to find a way how to employ the column years within the Booleans.

-> Please advice on short notice, how to solve this? <-

(Or do I have to choose an entirely different approach) ?

Thanks a lot!!

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

9 Answers

Anand Kumar Mar 02, 2017 at 09:38 AM
0

Hello,

can you attach bex query designer settings?

regards,

anand

Share
10 |10000 characters needed characters left characters exceeded
Chris Wood Mar 02, 2017 at 10:29 AM
0

Hello Anand, thank you for your reply. Sorry I didn't notice earlier.

Which settings do you need to know in particular?

Share
10 |10000 characters needed characters left characters exceeded
Anand Kumar Mar 02, 2017 at 10:42 AM
0

Hi,

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

Regards,

Anand

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hang on, creating screenshots...

0
Chris Wood Mar 02, 2017 at 11:08 AM
0
Share
10 |10000 characters needed characters left characters exceeded
Anand Kumar Mar 02, 2017 at 01:14 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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.

0
Chris Wood Mar 02, 2017 at 01:43 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Deepak Kumar Shah
Mar 02, 2017 at 04:43 PM
0
Share
10 |10000 characters needed characters left characters exceeded
Loed Despuig Mar 06, 2017 at 06:39 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Mustafa Bensan Mar 10, 2017 at 03:02 PM
0

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.


Share
10 |10000 characters needed characters left characters exceeded