Use Index Numbers to refer to Items in a Pivot Calc

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Use Index Numbers to refer to Items in a Pivot Calc

Post by Rudi »

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.
1.gif
2.gif
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.