Skip to Content
Technical Articles
Author's profile photo Vadim Kalinin

How to perform calculations in BPC script logic

In this article I will talk about calculations in the script logic launched by DM packages. Default.lgf require different approach, please read: How-To: Write default.lgf

In general the correct way is to scope the existing data and write to destination using WHEN/ENDWHEN aggregation.

For example, we have 5 accounts in ACCOUNT dimension: A1, A2, A3, A4, A5

A1, A2, A3 – contain some values, A4 – some percent and A5 is target account.

We want to calculate:

[ACCOUNT].[A5] = [ACCOUNT].[A1] + [ACCOUNT].[A2] * [ACCOUNT].[A4] – [ACCOUNT].[A3]

The scope will be: A1, A2, A3 (we don’t need to scope A4)

Sample 1:

*XDIM_MEMBERSET ACCOUNT=A1,A2,A3

*WHEN ACCOUNT
*IS A1
  *REC(EXPRESSION=%VALUE%,ACCOUNT=A5)
*IS A2
  *REC(EXPRESSION=%VALUE%*[ACCOUNT].[A4],ACCOUNT=A5)
*IS A3
  *REC(EXPRESSION=-%VALUE%,ACCOUNT=A5)
*ENDWHEN

The result will be accumulated in A5.

In case of multiple dimensions – multiple nested WHEN/ENDWHEN loops to be used:

([ACCOUNT].[A5],[AUDITTRAIL].[DS1]) = ([ACCOUNT].[A1],[AUDITTRAIL].[DS1]) + ([ACCOUNT].[A2],[AUDITTRAIL].[DS1]) * ([ACCOUNT].[A4],[AUDITTRAIL].[DS1]) – ([ACCOUNT].[A3],[AUDITTRAIL].[DS2])

Sample 2:

*XDIM_MEMBERSET ACCOUNT=A1,A2,A3
*XDIM_MEMBERSET AUDITTRAIL=DS1,DS2

*WHEN AUDITTRAIL
*IS DS1
  *WHEN ACCOUNT
  *IS A1
    *REC(EXPRESSION=%VALUE%,ACCOUNT=A5)
  *IS A2
    *REC(EXPRESSION=%VALUE%*[ACCOUNT].[A4],ACCOUNT=A5)
  *ENDWHEN
*IS DS2
  *WHEN ACCOUNT
  *IS A3
    *REC(EXPRESSION=-%VALUE%,ACCOUNT=A5,AUDITTRAIL=DS1)
  *ENDWHEN
*ENDWHEN

At the end I want to show an example of incorrect script for sample 1.

Sample 3:

*XDIM_MEMBERSET ACCOUNT=A1

*WHEN ACCOUNT
*IS * //A1 scoped
  *REC(EXPRESSION=%VALUE%+[ACCOUNT].[A2]*[ACCOUNT].[A4]-[ACCOUNT].[A3],ACCOUNT=A5)
*ENDWHEN

If the record is missing for account A1, then the result will be empty – incorrect!

P.S. More complex examples of script logic calculations can be found in my blog: PERIODIC <-> YTD conversion using script logic in BPC NW

Questions? Comments?

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo harish chint
      harish chint

      Hi Vadim Kalinin,

      thanks a lot for your article.

      Author's profile photo Anand Keer
      Anand Keer

      Hi Vadim Kalinin,

      Thanks for your article but still i am not be able to get the revenue even after using all the logics that you have given.

      actually i have taken all the stuff like QTY,PRICE,AMOUNT in 1 dimension that is in the KEYFIGURE.

      now i am trying to calculate the amount by multiplying the price and qty but still it not reflecting in the amount figure.

      I would appreciate if you will give some Idea to calculate the amount by using script logic.

      I have taken only 1 account of sales.

      Please suggest me some logic through which i can calculate the amount automatically .