Calculated Pivot Field Woes...

[Goose306]

Strange issue I'm hoping someone can help me out on:

I have a tall data set that has an amount tied to a person and a date, say like such (as a simplified version - the actual data has several thousands of lines for individual people stretching back 90 days):
<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC; } .tableizer-table th { background-color: #104E8B; color: #FFF; font-weight: bold; }</style>
<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC; } .tableizer-table th { background-color: #104E8B; color: #FFF; font-weight: bold; }</style>

Date	Name	Amount
7/10/2016	Ann	$1.15
7/10/2016	Ann	$2.20
7/10/2016	Bob	$11.19
7/11/2016	Ann	$12.22
7/11/2016	Bob	$8.50
7/12/2016	Ann	$9.22
7/12/2016	Ann	$51.92
7/12/2016	Bob	$26.51
7/12/2016	Bob	$1.12
7/12/2016	Laura	$52.18

<tbody>
</tbody>

I have that data in a Pivot Table. I want to create a calculated field to summarize the Amount column for a week. Here is the formula I am using:
<datevalue("07 17="" 2016")),amount,)[="" code]
<datevalue("07 17="" 2016")),amount,)[="" code]
<datevalue("07 17="" 2016")),amount,)

This adds with no errors, however it will only summarize the value if that individual person only had a single entry during that week, otherwise it returns $0. As an example, in the example data set, both Ann and Bob would return $0, and Laura would return $52.18, since she only has a single entry.

This is making me pull my hair out as the logic it seems to be operating on makes no sense. Any help is appreciated!</datevalue("07></datevalue("07></datevalue("07>

 

[Goose306]

Bump

 

[Goose306]

So, on further playing with it, the issue is it is comparing the Date field as a SUM of the dates - as we know, Excel stores dates as a raw number, and Excel is taking the sum of all the amounts during the specified timeframe and attempting to use that as a date (so, since it is adding two dates together, it comes across as something like 1/31/2166 in date format, for something where there is 2 amounts).

This seems poorly constructed, but I can't find a way to get around it cleanly. I can't use the group option given how my data works and how I need my end-result data to appear. Does anyone have any suggestions on how to best tackle this?