Skip to Content

There are a lot of cases when the user have to enter some coefficients and use script logic to calculate finance figures based on this coefficients. The examples of coefficients are: prices, discount, tax percents and other. The coefficients are entered on the base member level but in reports we need to show average values of the coefficients on the node level. For example: average price per quarter, average discount per product group…

There is no simple way to achieve this goal, BPC will simply sum the base member values of the coefficients and show this sum as a node value – absolutely useless.

My proposal is to define additional member for each coefficient member and use dimension formula for the additional member to calculate coefficient from summable members.

Below you can see a simplified real life example:

In our system we are planning advertisement sales for different titles in different legal entities and of different types in a separate cube.

We have 3 types of adv sales: Display, Classified and JobOpps. For types we don’t use separate dimension, we have members dedicated for each type (with the corresponding P&L lines in the main financial planning cube).

Members of the ACCOUNT dimension:

DATA ENTRY:

*** Price per adv page

PriceD

PriceC

PriceJ

*** Number of adv pages

PagesD

PagesC

PajesJ

*** Planned discount

DiscountD

DiscountC

DiscountJ

DEFAULT.LGF SCRIPT CALCULATED:

***Gross Sales

GrossSalesD

GrossSalesC

GrossSalesJ

*REC(EXPRESSION=[ACCOUNT].[PriceD]*[ACCOUNT].[PagesD],ACCOUNT=GrossSalesD)

*REC(EXPRESSION=[ACCOUNT].[PriceC]*[ACCOUNT].[PagesC],ACCOUNT=GrossSalesC)

*REC(EXPRESSION=[ACCOUNT].[PriceJ]*[ACCOUNT].[PagesJ],ACCOUNT=GrossSalesJ)

*** Net Sales

NetSalesD

NetSalesC

NetSalesJ

*REC(EXPRESSION=[ACCOUNT].[PriceD]*[ACCOUNT].[PagesD]*(1-[ACCOUNT].[DiscountD]),ACCOUNT=NetSalesD)

*REC(EXPRESSION=[ACCOUNT].[PriceC]*[ACCOUNT].[PagesC]*(1-[ACCOUNT].[DiscountC]),ACCOUNT=NetSalesC)

*REC(EXPRESSION=[ACCOUNT].[PriceJ]*[ACCOUNT].[PagesJ]*(1-[ACCOUNT].[DiscountJ]),ACCOUNT=NetSalesJ)

DIMENSION FORMULA MEMBERS:

*** Calculated discount to be used in reports – with average in nodes

DiscountCalcD = IIF([NetSalesD]=0,NULL,([GrosSalesD]-[NetSalesD])/[NetSalesD])

DiscountCalcC = IIF([NetSalesC]=0,NULL,([GrosSalesC]-[NetSalesC])/[NetSalesC])

DiscountCalcJ = IIF([NetSalesD]=0,NULL,([GrosSalesJ]-[NetSalesJ])/[NetSalesJ])

*** Calculated price to be used in reports – with average in nodes

PriceCalcD = IIF([PagesD]=0,NULL,[GrosSalesD]/[PagesD])

PriceCalcC = IIF([PagesC]=0,NULL,[GrosSalesC]/[PagesC])

PriceCalcJ = IIF([PagesJ]=0,NULL,[GrosSalesJ]/[PagesJ])

In the different reports we are able to see correct base and node figures (TIME nodes, TITLES nodes, ENTITY nodes) with all standard MEASURES (PERIODIC, YTD, QTD) for:

PagesD

PagesC

PajesJ

GrossSalesD

GrossSalesC

GrossSalesJ

NetSalesD

NetSalesC

NetSalesJ

DiscountCalcD

DiscountCalcC

DiscountCalcJ

PriceCalcD

PriceCalcC

PriceCalcJ

B.R. Vadim

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply