Formula to collect average of Amount per G/L Account

[Former Member]

Hello Experts,

I just started working on BPC and have been given a scenario to work on.

I will be loading data from BW to BPC and I would like to collect the AMOUNT value from BW into BPC as the average of all amounts per G/L Account.

G/L Account     Amount

ACC001               120

ACC001               130

ACC002               150

ACC002               160

ACC002               180

ACC003               200

ACC003               200

I would like the output in BPC to be -

ACC001               125

ACC002               245

ACC003               200

Kindly guide me in this scenario.

[Former Member · Accepted solution]

Hi Leonel,

User routine in your transformation file to cater for this case.

Andy

[Former Member · Accepted solution]

Hello Andy,

User routine? As I am new to BPC, I don't really know the steps to create one. Could you please guide me.

[former_member186338 · Accepted solution]

Sorry, but can you explain what are the figures you want to get average? What is the business idea behind? In general it looks absolutely strange and will create a lot of issues!

[Former Member · Accepted solution]

Hi Leonel,

Do you know ABAP?

You need a ABAP resource to do the routine for you.

Andy

[Former Member · Accepted solution]

Hello Andy,

Yes, I do have a good knowledge of ABAP.

Leonel

[Former Member · Accepted solution]

Hello Andy,

Are you talking about implementing a BAdI?

Thanks,

Leonel

[former_member186338 · Accepted solution]

If you want to create badi instead of 5-10 lines of script logic - you can do it !

[Former Member · Accepted solution]

better 5 -10 lines of script logic . Also will be able to learn something new.

[former_member186338 · Accepted solution]

In general script logic has only about 11 useful keywords For any person having at least some knowledge about programming - one day is maximum to become script logic developer

Please specify what you want to ask user?

Month list or just year?

Restrictions for other dimensions?

[Former Member · Accepted solution]

- Year List

- No restrictions for other dimensions.

[former_member186338 · Accepted solution]

What do you mean by year list?

[Former Member · Accepted solution]

In your earlier response, you asked me what I want to ask the user and if it is month list or just year. Could you kindly emphasize more on your query.

[Former Member · Accepted solution]

Hello Vadim,

There are no restrictions for the other dimensions, but regarding month list or year, I don't know what you mean by that. Are you asking whether the data in the report is displayed month wise or just yearly.

Please clarify.

Thanks.

[former_member186338 · Accepted solution]

You want to create budget figures based on prev actuals. What do you want the system to ask user?

For example - please select year for budget?

Or???

Is it clear?

[former_member186338 · Accepted solution]

And it's not about report - it's about DM package that will create data in the PLAN category!

The package will be launched once to create figures and then you can view figures in any report!

[Former Member · Accepted solution]

Yes, crystal. Thanks for clarifying that, Vadim.

I would like to create the budget figures for (CURRENT YEAR + 1) based on already existing Actuals. Is there a possibility of overlooking the Input Selections?

Thanks.

[former_member186338 · Accepted solution]

Current year - absolutely dangerous idea, the user has to select year himself!

For example - combobox with a list of years for budget creation.

If user select 2016 then budget of 2016 will be calculated as average of 2015 and 2014 (per month).

Is it correct?

[Former Member · Accepted solution]

Yes, that is correct.

You know better, if you say so, it is dangerous, then I shall not go around that path and follow the 'Input Selection'.

Thanks you Vadim.

[former_member186338 · Accepted solution]

Then - easy:

First read how to configure COMBOBOX prompt to pass the year to the script variable $Y$

Script itself:

*FOR %M%=$Y$.01,$Y$.02,$Y$.03,$Y$.04,$Y$.05,$Y$.06,$Y$.07,$Y$.08,$Y$.09,$Y$.10,$Y$.11,$Y$.12

*XDIM_MEMBERSET TIME=TMVL(-1,%M%),TMVL(-2,%M%) //Month of prev and prev prev year

*WHEN TIME

*IS * //scoped

*REC(EXPRESSION=%VALUE%/2,TIME=%M%) //Average calc

*ENDWHEN

*NEXT

That's all

Vadim

[Former Member · Accepted solution]

So how would we write a Script Logic for that? Kindly advise.

[Former Member · Accepted solution]

Thank you Vadim.

Will get back to you if I face any other issues while scripting.

Leonel.

[Former Member · Accepted solution]

Hello Vadim,

One final question.

I am loading my data in BPC from BW Info Provider. So when I run the BPC Process Chain, I run the PC for Loading first and then I run the Formula Process Chainsecond. Am I correct?

Leonel.

[former_member186338 · Accepted solution]

First you load actual figures from BW.

For example you have loaded data for some years.

Then at the time you decided to create future Plan figures based on previous actual average - you launch DM package with the script.

Vadim

[Former Member · Accepted solution]

Hello Vadim,

Good Day to you. Sorry to be bothering you once again. I have come across some difficulties in the same issue. I kindly request your guidance.

After loading sample data in BPC, please find screenshot as below -

Post this I ran the Script Logic Package - This was successful, PFB

I refreshed the report, as per logic the data for 2015 should be displayed as Average of Prev two years. But I still get blank values for 2015.TOTAL column -

Kindly help.

[former_member186338 · Accepted solution]

What script you are using?

In my script the destination category was - Plan, and in your report - it's Actual! Why do you looking for Actual in 2015???

[Former Member · Accepted solution]

*FOR %M% = $BUDGET_YEAR$.01,$BUDGET_YEAR$.02,$BUDGET_YEAR$.03,$BUDGET_YEAR$.04,$BUDGET_YEAR$.05,$BUDGET_YEAR$.06,$BUDGET_YEAR$.07,$BUDGET_YEAR$.08,$BUDGET_YEAR$.09,$BUDGET_YEAR$.10,$BUDGET_YEAR$.11,$BUDGET_YEAR$.12

*XDIM_MEMBERSET TIME = TMVL(-1,%M%), TMVL(-2,%M%)

*WHEN TIME.ID

*IS *

*REC(EXPRESSION = %VALUE%/2, TIME = %YEAR%.TOTAL, CATEGORY_VTYPE = PLAN)

*ENDWHEN

*NEXT

[Former Member · Accepted solution]

Please ignore the Script that I sent earlier;

*FOR %M% = $BUDGET_YEAR$.01,$BUDGET_YEAR$.02,$BUDGET_YEAR$.03,$BUDGET_YEAR$.04,$BUDGET_YEAR$.05,$BUDGET_YEAR$.06,$BUDGET_YEAR$.07,$BUDGET_YEAR$.08,$BUDGET_YEAR$.09,$BUDGET_YEAR$.10,$BUDGET_YEAR$.11,$BUDGET_YEAR$.12

  *XDIM_MEMBERSET TIME = TMVL(-1,%M%), TMVL(-2,%M%)

  *XDIM_MEMBERSET CATEGORY_VTYPE = 20

  *WHEN TIME

  *IS *

  *REC(EXPRESSION = %VALUE%/2, TIME = %M%,CATEGORY_VTYPE = 20)

  *ENDWHEN

*NEXT

[former_member186338 · Accepted solution]

And what do you have for CATEGORY_VTYPE = 20 on the report?

P.S. And what do you mean by:

*XDIM_MEMBERSET CATEGORY_VTYPE = 20?????

[Former Member · Accepted solution]

In BW we have values for both 10(ACTUAL) and 20(PLAN) til 2014 after for 2015 I want to determine the value for 20(PLAN) in BPC.

[former_member186338 · Accepted solution]

Then you have to scope what you read as:

*XDIM_MEMBERSET CATEGORY_VTYPE =10 //ACTUAL!!!

And target will be CATEGORY_VTYPE =20

And show the report...

[former_member186338 · Accepted solution]

And please read:

And use UJKT for script testing

[Former Member · Accepted solution]

Hello Vadim,

I tried testing using the process you mentioned above.

I got the below exception when I tried validating.

UJK_VALIDATION_EXCEPTION:Invalid TMVL return value

Kindly advise.

[former_member186338 · Accepted solution]

Sorry, but you are asking questions without providing required info... It's an endless process!

Where is the screenshot of UJKT screen?

[Former Member · Accepted solution]

Hello Vadim,

Sorry.

Please find the screenshot for the same exception in UJKT.

Thanks,

Leonel.

[former_member186338 · Accepted solution]

First - remove spaces:

Not: BUDGET_YEAR = 2015; But: BUDGET_YEAR=2015;

Second - my small mistake:

Not: *XDIM_MEMBERSET TIME = TMVL(-1,%M%), TMVL(-2,%M%)

But: *XDIM_MEMBERSET TIME = TMVL(-12,%M%), TMVL(-24,%M%)

And don't fill Data Region - useless for this script!

Result:

Vadim

[Former Member · Accepted solution]

Hello Vadim,

It has successfully written the record for PLAN data for 2015.

Please find the screenshot.

Extremely thankful for your patience and guidance with this scenario. Hope to learn a lot more from you in the future.

[Former Member]

Hello Experts,

Apologies for not clearly stating my requirements earlier. After going through my requirements once again, I would like to share the same with you as well.

Please find the sample report for your reference.

For the PLAN field (2015.TOTAL), I would like to calculate the AVERAGE(2013.TOTAL:2014.TOTAL), that is, AVERAGE of ACTUAL values will determine the PLAN values.

PLAN(2015.TOTAL) = AVERAGE(2013.TOTAL:2014.TOTAL)

In this report which I have shared for your reference, I have done the calculation so that you will understand what I am talking about. What I would like for the report to do is, run this calculation at runtime.

Every time the report is opened or refreshed I want this calculation to run. I am not sure how this is done; In BW, we have CALCULATED KEYFIGURES, but in BPC, I am not sure how to work about on this.

Requirement 1: Calculate PLAN value as AVERAGE(ACTUAL values).

Requirement 2: Forecasting values for next two years. ( Please help if anyone has idea on this as well).

Kindly advise.

[former_member186338]

Hi Leonel,

For your requirements ii's better to use Excel formulas in the local member to calculate average. But still it looks like you don't have full understanding of the requirements...

Vadim

[Former Member]

Hello Vadim,

The task that has been given to me is a sample and not a proper requirement.
I guess the requirement as of now what is required of me is to identify the PLAN value from the ACTUAL values and determine this PLAN value during runtime.

I understand we can use EXCEL Formulae to calculate this but is there anyway to retain this formula every time this report is opened and run / refreshed.

[former_member186338]

I think you guess wrong... Nobody determine Plan figures during runtime

If you want to create figures for Plan using some algorithm (like average of previous 2 yeas) - then you can run some script to perform calculation and store the result in the Plan category...

But not during report execution!

[Former Member]

Hi Leonel,

Look up on how to use a local member, or you can create a calculated member.

Those two options does not store the value in BPC.

If you want the value stored in BPC then use BADI.

can be routine (when you load data) or custom logic independent calculation.

Andy

[Former Member]

Hello Vadim,

Thank you. If I am running a Logic Script, I hope I should be able to find good amount of information from your blog.

I will get back to you. Thanks.

[Former Member]

Hi Andy,

I want to calculate the PLAN values based on Previous Two Years data within BPC. Kindly advise on the best possible option.

Leonel.

[Former Member]

Hi Leonel,

If you want to store those values then use BADI.

I would suggest using custom logic. then you can run on demand.

Andy

[former_member186338]

Hi Andy,

What is the reason to create badi for calculation of already loaded actuals???

Script will do the job.

Vadim

[Former Member]

Hi Vadim,

This is one of the solutions, there are many ways to do it, but this is my preferred way.

Script will do the job, so as ABAP.

Andy

[former_member186338]

Hi Andy,

The main issue of the abap badi is support question! There are a lot of messages here when badi stop working after new SP etc... And with script the person without abap knowledge can test and correct!

Vadim

[Former Member]

Hi Vadim,

the same thing can be said about scrip logic, you need someone who knows scrip logic to support the system. there are more and more ABAP consultants getting involved with BPC but less and less consultants knows script logic, so using BADI actually will be easier to support, a company have many ABAP in their team, but hardly any scrip logic consultant.

There are many messages here asking for script logic support as it is complex and difficult to debug.

there are pros and cons for each BADI and script logic.

You are pro script logic and I'm pro BADI.

Andy

[Former Member]

Hello Vadim,

I stand corrected. Thank you for the rectification of my error.

I want to create figures for the Plan Category, as you mentioned, from the previous 2 year using Script Logic.

If I have to use a Script Logic, I would like some guidance from your expert knowledge on how to go about doing the same. Could you kindly explain me with some sample data please.

Much obliged.

Thank you,

Leonel

[Former Member]

Shouldn't the amount on ACC002 be 163,3333 as (150+160+180)/3?

[Former Member]

Yes sorry for the confusion there...yes it should be 163.3333

[former_member186338]

Average??? What is the business logic?