G
Guest
Underlying data has several attributes plus shares. It also uses a lookup
field to populate a price and date column. Looks like this (not all
attributes listed):
Account Dealer Branch Rep Fund AcctType Shares Price Date
The pivot table essentially summarizes the data using all attributes listed
above except Account (i.e. takes it up one level). Here's the catch. The
pivot table is being used to replicate the results of a program for testing
purposes. The program is rounding at a specific breakpoint - Dealer, Branch,
Rep, Fund, AcctType - meaning that it is summarizing shares at the level
indicated, then multiplying the results for that level by the price for the
corresponding fund and rounding to 2 decimals to obtain a base record. All
further program summarizations represent the addition of one or more of these
base records - ensuring no further rounding occurs.
Pivot table columns are therefore:
Dealer Branch Rep AcctType Fund Price 'Base Record Shares'
where 'Base Record Shares' is the Sum of Shares for that level from the
underlying data.
I have added a Calculated Field as follows:
Based Record Values = ROUND(Shares*Price,2)
Since I was unable to do the price lookup in the formula, I added it as a
column in the underlying data so i could use it in this formula. The formula
is trying to replicate the rounded base value from the program - taking a
share summary and multiplying it by the price and then rounding the result.
Here are some sample values:
Dealer Branch Rep Acct Type Fund Price Base Record Shares Base Record
Value
1234 000 ABCD Rtmt 427 27.42 3,845.569
210,891.00
460 9.78 18,960.833
185,436.95
As you can see, the Base Record Value is twice the value it should be for
fund 427 (3,845.569 x 27.42 = 105,445.50) and 5.61 times the value it should
be for fund 460. This is consistent throughout the pivot table despite the
number of accounts varying from 0 to 5 for any given summarization.
In fact, in the example above, there are three accounts for fund 427 that
should be included in the total (one with zero shares) and if I change the
shares data field from sum to count it only shows 2 accounts and only
includes the value of one. Actual correct value for the first record is:
Shares = 5,727.907
Value = 157,059.20
This behavior is not consistent - some share summarizations are correct.
Anyone have a clue what might be going on here?
field to populate a price and date column. Looks like this (not all
attributes listed):
Account Dealer Branch Rep Fund AcctType Shares Price Date
The pivot table essentially summarizes the data using all attributes listed
above except Account (i.e. takes it up one level). Here's the catch. The
pivot table is being used to replicate the results of a program for testing
purposes. The program is rounding at a specific breakpoint - Dealer, Branch,
Rep, Fund, AcctType - meaning that it is summarizing shares at the level
indicated, then multiplying the results for that level by the price for the
corresponding fund and rounding to 2 decimals to obtain a base record. All
further program summarizations represent the addition of one or more of these
base records - ensuring no further rounding occurs.
Pivot table columns are therefore:
Dealer Branch Rep AcctType Fund Price 'Base Record Shares'
where 'Base Record Shares' is the Sum of Shares for that level from the
underlying data.
I have added a Calculated Field as follows:
Based Record Values = ROUND(Shares*Price,2)
Since I was unable to do the price lookup in the formula, I added it as a
column in the underlying data so i could use it in this formula. The formula
is trying to replicate the rounded base value from the program - taking a
share summary and multiplying it by the price and then rounding the result.
Here are some sample values:
Dealer Branch Rep Acct Type Fund Price Base Record Shares Base Record
Value
1234 000 ABCD Rtmt 427 27.42 3,845.569
210,891.00
460 9.78 18,960.833
185,436.95
As you can see, the Base Record Value is twice the value it should be for
fund 427 (3,845.569 x 27.42 = 105,445.50) and 5.61 times the value it should
be for fund 460. This is consistent throughout the pivot table despite the
number of accounts varying from 0 to 5 for any given summarization.
In fact, in the example above, there are three accounts for fund 427 that
should be included in the total (one with zero shares) and if I change the
shares data field from sum to count it only shows 2 accounts and only
includes the value of one. Actual correct value for the first record is:
Shares = 5,727.907
Value = 157,059.20
This behavior is not consistent - some share summarizations are correct.
Anyone have a clue what might be going on here?