Unable to read from a custom measure

[former_member605951]

Hi Experts,

For performing the entity allocation in our project, we have a to consider the last 13 periods average of the Entity actual data. To do so, we have created a custom measure named R13, which holds the average data for last 13 actual periods. Using this measure, we have designed the script logic to read the R13(custom) measure data and on top of which we are calculating the allocation(%) values which will be used for allocating the forecast data.

The issue we are experiencing is that the data is not being read from the R13(custom) measure and it is by default taking the Periodic measure and also posting to the Periodic measure. Also, while trying to using this custom measure in input schedule, it is working fine and reads the value from it.

We are using BPC 10.1 NW Standarad, SP 13.

Please find the script logic below .Any leads on the above scenario would be of great help. Thank you!

*XDIM_MEMBERSET P_CATEGORY = %P_CATEGORY_SET%

*XDIM_MEMBERSET P_CUSTOMER = %P_CUSTOMER_SET%

*XDIM_MEMBERSET P_TIME = %P_TIME_SET%,2020.INP

*XDIM_MEMBERSET P_PACKAGE = %P_PACKAGE_SET%

*XDIM_ADDMEMBERSET MEASURES = R13, PERIODIC

*XDIM_ADDMEMBERSET P_ENTITY = BAS(PB)

*WHEN P_TIME

*IS %P_TIME_SET%

*WHEN P_PACKAGE

*IS %P_PACKAGE_SET%

*WHEN P_CATEGORY

*IS ACTUAL

*WHEN P_CUSTOMER

*IS %P_CUSTOMER_SET%

*WHEN P_DATASRC

*IS CONSUMPTION

*WHEN P_ACCOUNT

*IS 1000

*WHEN P_ENTITY

*IS BAS(PB)

*WHEN MEASURES

*IS R13

*REC(EXPRESSION=(%VALUE%)/([P_CATEGORY].[ACTUAL],[P_PROMO].[NO_PROMO],[P_DATASRC].[CONSUMPTION],[P_INSTANCE].[NO_IN],[P_CUSTOMER].[TOT_DSD],[P_PACKAGE].[TOT_PKG],[P_ENTITY].[PB]),P_DATASRC=USER_INPUT,P_TIME=2019.INP,P_ACCOUNT=WKPCT)

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

[former_member186338 · Accepted solution]

Just performed simple test in the ENVIRONMENTSHELL copy - model PLANNING (PERIODIC):

Measure R6 - average for last 6 periods:

'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP"), SUM(LASTPERIODS(6,[%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])/6,[MEASURES].[/CPMB/SDATA])';SOLVE_ORDER=3

Script:

*XDIM_MEMBERSET ACCOUNT=PL110
*XDIM_MEMBERSET ENTITY=DE
*XDIM_MEMBERSET INTERCO=ThirdParty
*XDIM_MEMBERSET CATEGORY=Budget
*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)
*XDIM_MEMBERSET PRODUCT=ProductA
*XDIM_MEMBERSET AUDITTRAIL=Input
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET MEASURES=PERIODIC

*WHEN ACCOUNT
*IS *
*REC(EXPRESSION=[MEASURES].[R6],ACCOUNT=PL120)
*ENDWHEN

Result:

No issues, R6 measure for PL110 copied to PL120

P.S. BPC on BW 750 SP12 without HANA

[former_member605951 · Accepted solution]

Hi Vadim,

Thank you for your input. We will try the solution and update you soon.

[former_member186338 · Accepted solution]

P.S. Always start with something simple!

[former_member605951 · Accepted solution]

Hi Vadim,
Thank you for providing us a sample code, tried likewise and it works.

[former_member224107]

Hi,

you wil need to specify the MEASURES also in the scope :

*XDIM_MEMBERSET MEASURES = PERIODIC

[former_member605951]

Hi,

Yes it is already part of the scope. Still gives the error.

[former_member224107]

is PERIODIC your storage type ?

[former_member224107]

Hi Sindhuja, you are able to use the custom measure in the logic, using it directly in the EXPRESSION statement.

*REC(EXPRESSION=[MEASURES].[R13]/([P_CATEGORY].[ACTUAL],[P_PROMO].[NO_PROMO],[P_DATASRC].[CONSUMPTION],[P_INSTANCE].[NO_IN],[P_CUSTOMER].[TOT_DSD],[P_PACKAGE].[TOT_PKG],[P_ENTITY].[PB],

[MEASURES].[R13]

),P_DATASRC=USER_INPUT,P_TIME=2019.INP,P_ACCOUNT=WKPCT)

However, you need to be careful where your trigger is for the calculation. If you scope in the current period, and if no value exists for a certain combination where we had data in the past, then nothing will get calculated for this combination. You have to analyze whether this situation will occur. Alternatively, however, you can use a full MDX expression to overcome that issue.

Example

*SELECTCASE [TIME].CURRENTMEMBER.NAME
*CASE %P_TIME_SET%

[P_ACCOUNT].[#WKPCT] = ([P_ACCOUNT].[1000],[P_DATASRC].[CONSUMPTION],[MEASURES].[R13])

*ENDSELECT

*COMMIT

[former_member605951]

Hi Wouter,

We have tried using custom measure directly in the REC statement, but were not successful. This is the error message we get:

UJK_EXECUTION_EXCEPTION:MEASURES not specified

[former_member186338]

I do not recommend using custom measure in script logic. The performance can be terrible 🙂

[former_member224107]

If you are running on HANA, the performance is ok. If HANA_MDX is activated,

it is possible that part of that MDX is executed in HANA.

[former_member186338]

I don't see HANA in the question author system 🙂

[former_member224107]

HANA is now such a commodity that it is not mentioned anymore 🙂

Off course, performance could be an issue, but he will be able to assess whether

this is a bottleneck or not. Using the measure the script will be much more readable.

[former_member186338]

The logic description is still not clear!

In general:

If you want to calculate average for single month based on 13 previous months you can use TMVL with properly maintained TIMEID property

Another option is to create 13 properties in TIME dimensions and use LOOKUP

[former_member605951]

Hi Vadim,

Below table will explain you what is the actual requirement and why we are calculating the latest 13 period actual sum.

The First table shows the actual periods for 3 entities N,S and W. The highlighted WK15 is the last actual period maintained as part of category dimension. Our Logic will lookup this value and go back to the latest 13 periods and calculate the % values.

As per the table, we will derive %, for each entity. We have an input form through which the user will enter aggregated forecast data to dummy Entity. With the calculated % value, we need to find the amount from the user entered total and the same need to be allocated to the remaining forecast weeks respective to every entity.

The second table shows the user input and the total calculation from the derived percentages.

The results against each entity will be copied down to the remaining forecast periods of the year as shown in the last table.

Please let us know your inputs on the same.

Thanks.

[former_member605951]

Hi Vadim,

Thank you for your response.

We are currently driving the TIME dimension using WEEKS as base members, so we were unsuccessful in maintaining the TIMEID's as some weeks have a split across two months.
We have one property LAST_ACT_PER in the CATEGORY Dimension holding the last actual period (week) value.
Using this property, we were not able to calculate the averages for the last 13 actual periods using the logic script.

Could you please guide us how to proceed with calculating the averages using the logic script?
Thank you!

[former_member186338]

"We are currently driving the TIME dimension using WEEKS as base members, so we were unsuccessful in maintaining the TIMEID's as some weeks have a split across two months." - looks like you misunderstand TIMEID property!

TIMEID is a string containing some sequential number - nothing more!

Like 2019001,2019002,...,2019052

[former_member186338]

P.S. Can you explain the required calculation logic of the script once again! Using words and data samples, not using incorrect script code...

[former_member186338]

"Also our TIME dimension does not have TIMEID's" - sorry, but why??? Incorrect setup!

And why not to create additional properties???

Unable to understand!

[former_member605951]

version-screenshot-1.pngversion-screenshot-2.png

Hi Vadim,

Thank you for your response.

To calculate the average of the last 13 actual periods, we do not have any specific property through which we can derive those periods. Also our TIME dimension does not have TIMEID's and the members in TIME dimension are are in weeks time-dimension.png as attached in screenshot.
Without TIME ID's, we are not able to leverage the TMVL function to get the 13 period calculation done.

Could you please help us how the average calculation can be achieved using script logic? Attaching the BPC version screenshots for reference.

Thank you!

[former_member186338]

I will not answer questions where "Insert File" is used instead of correct "Insert Image"

Please correct!

[former_member605951]

Hi Vadim,

Apologies for the error. Correcting the query:
To calculate the average of the last 13 actual periods, we do not have any specific property through which we can derive those periods. Also our TIME dimension does not have TIMEID's and the members in TIME dimension are are in weeks as below-

Without TIME ID's, we are not able to leverage the TMVL function to get the 13 period calculation done.

Could you please help us how the average calculation can be achieved using script logic? Attaching the BPC version screenshots for reference.

Thank you!

[former_member186338]

Instead of custom measure you have to calculate average in script logic!

P.S. Please read:

https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/