Pivot table summed field query

[Riccardo S]

Hi all, I have a pivot table based on stock levels over multiple weeks, most of the fields work as expected (transfer in/out, etc.) but one I'm having trouble with is 'Opening Qty'. The issue is that this field sums the 'Opening Qty' from each individual week in the range when I actually I just want it to show the 'Opening Qty' from the first week in the range. Any help would be appreciated.

Thanks

 

[Riccardo S]

For anyone interested my workaround so far has been index the worksheets as they're being read by my compiling routine, the weeks order being from left to right (1st=1, 2nd=2, etc.). This index is added into the compiling array which is then dumped into a data table along with the rest of the data. In the data table I've added a column named 'True Opening Qty' which contains the formula '=IF([Tab Index]=1,[@[Opening Qty]],0)' which basically copies the Opening Qty value to the column only if the tab index is 1 (the first week regardless of week number). From there I just used the 'True Opening Qty' in the pivot table and everything seems to work fine. I'd still be interested to here of any other/better ways of accomplishing this task though.