Calculating the Cost of Fish Used

The rule for the Inventory cube now contains statements to calculate the value of Purchase Cost for all dates and all DaysOld batches.

It's now a simple matter of subtraction to determine the value for Cost of Fish Used. Specifically, the Cost of Fish Used for any given date and DaysOld batch is determined by subtracting the Purchase Cost for the following date and DaysOld batch from the current date and DaysOld batch.

This sounds slightly confusing, so consider the following view of the Inventory cube. Remember that a batch of fish ages as days progress, so DaysOld batch 3 on May 2 becomes DaysOld batch 4 on May 3.

Screen showing a view of the Inventory cube.

Here you see that the Purchase Cost of trout in DaysOld batch 3 on May 2 is $9,070.25. The quantity in stock is 46.88 Kgs. (Note that Purchase Cost and Quantity in Stock - Kgs are measures of fish at the beginning of the day.)

The Purchase Cost of trout in DaysOld batch 4 on May 3 is $3,878.96, and the quantity in stock is 20.05 Kgs. Because both Purchase Cost and Quantity in Stock are reduced at the start of the day on May 3, you can surmise that some trout was used to make fishcakes on May 2.

You can determine the cost of trout used on May 2 by subtracting the Purchase Cost value of trout in DaysOld batch 4 on May 3 from the Purchase Cost value of trout in DaysOld batch 3 on May 2. 

$9,070.25 - $3,878.96 = $5,191.29

If you refer back to the preceding view, you see that $5,191.29is indeed the Cost of Fish Used for DaysOld batch 3 on May 2. The rule statement required to calculate this value is 

['Cost of Fish Used']=N:DB('Inventory',!FishType,!Date,!DaysOld,'Purchase
Cost')- DB('Inventory',!FishType,DNEXT('Date', !Date),DNEXT('DaysOld', 
!DaysOld),'Purchase Cost');

Calculating the Cost of Fish Used for DaysOld batch 6 requires a different rule statement, because there is no batch after 6. Once a batch of fish is 6 days old, it is (thankfully) removed from inventory and the entire purchase cost of the batch is charged to the cost of fish used. The following statement accomplishes this calculation.

['6','Cost of Fish Used']=['Purchase Cost'];

Recall that rules statements should always be ordered most-restrictive to least-restrictive according to area definition. In this case, the area ['6','Cost of Fish Used'] is more restrictive than the area ['Cost of Fish Used'], so the statements should appear in the following order:

['6','Cost of Fish Used']=['Purchase Cost'];
['Cost of Fish Used']=N:DB('Inventory',!FishType,!Date,!DaysOld,'Purchase
Cost')- DB('Inventory',!FishType,DNEXT('Date', !Date), DNEXT('DaysOld',
!DaysOld),'Purchase Cost');

The following steps illustrate how to add these statements to the rule for the Inventory cube.

Procedure

  1. Double-click the Inventory rule in the Server Explorer.
  2. Add the statements immediately beneath the last calculation statement in the rule.

    The statements must be in the order they appear in the example.

  3. Click Save to compile and save the rule.