This seems like something that should be so simple, but I can't figure it out this morning. I've even had my coffee. I am returning four fields from a WebFOCUS query: SHIPWEEK, Ship Date, Part Numberand Quantity (QTY). I set up the query so that it pulls 365 days of records when the workbook opens. I have then summarized the data into a Pivot Table. The Pivot Table simply shows SHIPWEEK as a row with two VALUES: Count of QTY and Sum of QTY. This all works fine.
What I want beyond this, however, is to calculate for the range of the returned results the average weekly count and standard deviation. I can do this simply by using the AVERAGE() and STDEV() functions and pointing to the two VALUES ranges in the Pivot Table. That will work once. But I'd like to not have to manually adjust the range for these formulas each time I run the query. Theoretically, there will be the same number of weeks in 365 days of data, but that may not always be the case. If the number of rows in the Pivot Table changes, then it might skew my results.
Surely there is a way to do this in the Pivot Table itself? Pivot Table offers a "Grand Total" option, which will sum all of the records, but I don't know how to do the equivalent of "Grand Total" for average or Standard Deviation.
If not in the Pivot Table, then dynamically outside of the Pivot Table without requiring manual range updates each time?
