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>[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Date[/TH]
[TH]Name[/TH]
[TH]Amount[/TH]
[/TR]
[TR]
[TD]7/10/2016[/TD]
[TD]Ann[/TD]
[TD]$1.15[/TD]
[/TR]
[TR]
[TD]7/10/2016[/TD]
[TD]Ann[/TD]
[TD]$2.20[/TD]
[/TR]
[TR]
[TD]7/10/2016[/TD]
[TD]Bob[/TD]
[TD]$11.19[/TD]
[/TR]
[TR]
[TD]7/11/2016[/TD]
[TD]Ann[/TD]
[TD]$12.22[/TD]
[/TR]
[TR]
[TD]7/11/2016[/TD]
[TD]Bob[/TD]
[TD]$8.50[/TD]
[/TR]
[TR]
[TD]7/12/2016[/TD]
[TD]Ann[/TD]
[TD]$9.22[/TD]
[/TR]
[TR]
[TD]7/12/2016[/TD]
[TD]Ann[/TD]
[TD]$51.92[/TD]
[/TR]
[TR]
[TD]7/12/2016[/TD]
[TD]Bob[/TD]
[TD]$26.51[/TD]
[/TR]
[TR]
[TD]7/12/2016[/TD]
[TD]Bob[/TD]
[TD]$1.12[/TD]
[/TR]
[TR]
[TD]7/12/2016[/TD]
[TD]Laura[/TD]
[TD]$52.18[/TD]
[/TR]
</tbody>[/TABLE]

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?