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?
