AjEGfmTech

Calculating Summary Fields

Discussion created by AjEGfmTech on Nov 6, 2012
Latest reply on Nov 6, 2012 by AjEGfmTech

I am looking to calculate the result of two summary fields. I tried using 'GetSummary' to get the result of the two fields but the calculated result ends up being wrong.

 

Background

This is a sales database. Daily sales are entered for each store. Each store has a Royalty and AdFund rate. Weekly totals are required along with the total weekly transfer (sales - (royalty+adfund)). The plays out as follows.

 

NetSales (daily records)

RateRoyalty (fixed)

RateAdFund (fixed)

 

Royalty (NetSales * RateRoyalty)

AdFund (NetSales * RateAdFund)

Transfer (Royalty + AdFund)

 

TotalNetSales (total of NetSales, sorted by StoreID)

TotalRoyalty (total of Royalty, sorted by StoreID)

TotalRoyalty (total of Royalty, sorted by StoreID)

TotalTransfer (total of Transfer, sorted by StoreID)

 

GetSumRoyalty (GetSummary (TotalRoyalty; StoreID)

GetSumAdFund (GetSummary (TotalAdFund; StoreID)

TotalTransferCalc (GetSumRoyalty + GetSumAdFund)

 

The problem has to do with 'TotalTransfer'. This amount frequently does NOT equal TotalRoyalty + TotalAdFund. This is probably because TotalTransfer is the sum total of each of the seven daily 'Transfer' values in the week and therefore is subject to a slight differences compared to when the sum totals are added to each other. Following is sample data.

 

RateRoyalty = 0.06

RateAdFund = 0.02

 

Day, NetSales, Royalty, AdFund, Transfer

Mon, 1565.36, 93.92, 31.31, 125.23

Tue, 1850.79, 111.05, 37.02, 148.06

Wed, 1703.38, 102.20, 34.07, 136.27

Thu, 2808.33, 168.50, 56.17, 224.67

Fri, 2173.52, 130.41, 43.47, 173.88

Sat, 2127.16, 127.63, 42.54, 170.17

Sun, 1508.27, 90.50, 30.17, 120.66

Totals, 13736.81, 824.21, 274.74, 1098.94

 

The last number should be 1098.95 if you take 824.21 + 274.74. That is the crux of the problem.

The thing is, that 'TotalTransferCalc' produces the same 1098.94 result?!? I would have expected (wanted) it to produce 1098.95. Why doesn't it?

 

Any help appreciated. Thanks.

Outcomes