Purchase Cost Calculation
Fishcakes International buys fish in several markets around the world. The company tracks these purchases by fish type, by market, and by date.
For each purchase, the number of kilos and the price per kilo in local currency are input directly into a cube named Purchase.
The following table lists the four dimensions related to fish purchases in the Purchase cube with their names and descriptions.
|
Dimension Name |
Description |
|---|---|
|
Fishtype |
Contains elements for each type of fish purchased. There are 19 leaf elements in the dimension, as well as a Total Fish Types consolidation. |
|
Market |
Contains elements for the markets in which fish are purchased. There are seven leaf elements in the dimension, as well as the Total Markets consolidation. |
|
Date |
Contains elements for the dates on which fish are purchased. The dimension contains 366 leaf elements, as well as multiple consolidations at varying levels. |
|
PurMeas |
Contains five leaf elements that measure fish purchases. Measure include Purchase Cost - LC, Quantity Purchased - Kgs, and Price/Kg - LC. |
Using rules, you need to calculate the purchase cost in local currency by writing one rule statement that defines purchase cost as the product of price and amount purchased. This one statement will apply to all fish, for all markets and all dates in the Purchase cube.
Using the element names from the PurMeas dimension and applying the rules syntax presented in the first section of this book, you can create the following calculation statement:
['Purchase Cost - LC'] = ['Quantity Purchased - Kgs']*['Price/Kg- LC'];This statement says that if the TM1® server is asked for any value that matches the area Purchase Cost - LC, it will multiply the corresponding values for Quantity Purchased - Kgs and Price/Kg - LC and return the answer.
When this calculation statement is in place, you can see that Purchase Cost - LC values are calculated in the Purchase cube:
But what about the value in the lower right cell? The calculation statement is multiplying Total, Quantity Purchased - Kgs by the Total, Price/Kg - LC. Of course, prices per Kg do not sum, so the value for Total, Purchase Cost - LC is incorrect.
The best way to generate the total Purchase Cost - LC is to sum purchase cost values across fish, which TM1 can do automatically through its dimensional consolidations.
You can specify that a rule applies only to leaf cells and not to consolidations such as Total. This is done by inserting an N: in front of the formula portion of the calculation statement:
['Purchase Cost - LC'] = N: ['Quantity Purchased - Kgs']*
['Price/Kg
- LC'];When the statement is corrected as above, consolidations are not calculated by the rule. Accordingly, the total Purchase Cost - LC consolidation is correctly calculated by summing the values of the members of the consolidation, as shown in the following figure.
