I can across a great article that taught me something I've never known about Calculated Items in a Pivot Table; the fact that you can use Index numbers (or offsets) in the defined calculation. A very interesting and handy feature to know...
See the full article here: Calculating the Value Fields in a PivotTable Report
Use Index Numbers to refer to Items while inserting calculated fields:
We have thus far been referring to items by their names, while inserting calculated fields. You can also refer to items by index numbers which determine an item in 2 ways: (i) either by its position in the PivotTable; or (ii) by its relative position in the PivotTable. To determine an item by its position using an index number, use the format Year[1] or Year[2] where Year is the name of the field and the numbers 1 or 2 represent the column number (or row number) in the Pivot Table, where the item to be included in the formula is positioned (Note: the index number does not count hidden items). To determine an item by its relative position, using an index number, use the format Year[+1] or Year[+2] where Year is the name of the field and the numbers 1 or 2 represent the number of columns to the right (or number of rows below) of where the item to be included in the formula is positioned, relative to the inserted calculated field. Index numbers are used ideally when the item name (viz. year, month, etc) change often but the calculations are based on a constant item order.
Use Index Numbers to refer to Items in a Pivot Calc
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Use Index Numbers to refer to Items in a Pivot Calc
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.