Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Calculated Item
Hello all,
I am trying to get a calculated item to work correctly. I have a pivot table with two row fields (code, description), three column fields (plan, elevation, date) and one data field (amount). I am using Excel 2000 on Windows 2000. The row fields have a one-to-one relationship with each other (each code has one description), therefore each code entry occupies only one line. Each plan has three elevation values, and each elevation has two date values. The calculation I am trying to achieve is the difference between the value (amount) of the first date entry and the second date entry for each cost code. What I have tried is to click on the date field in the column area, Formulas:Calculated Item. Then in the calculated item pop-up: click on the date field, click the most recent date and insert item, type a minus sign, then click the old date and insert item. Then click OK. Excel appears to hang at this point. However, I waited long enough (some fifteen plus minutes) and finally excel came back with a solution. However, the output was not what I wanted. The pivot table returned ALL description values for EACH code value in the row area, with the amount value populated only on the correct (corresponding) description line. It also appears to have sorted the descriptions WITHIN each code. The formula line calculates correctly on the one line per code that has values. What did I do wrong? Example output: I hope the formatting is legible. Plan1 Elv1 Code - Desc - Date1 - Date 2 - Formula 1 1D 100 150 50 5D 13D 8D 2D 2 1D 5D 13D 8D 2D 80 120 40 Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Calculated Item
Well, I came to the conclusion that this is a characteristic/limitation of
Pivot Tables. This behaviour will always occur if this is more than one row field. It can be solved in this instance because the Code and Description have a one-to-one relationship. If these values are concatenated in the original data, then the Pivot will be constructed with one row field, and the desired output achieved. "Dominic" wrote: Hello all, I am trying to get a calculated item to work correctly. I have a pivot table with two row fields (code, description), three column fields (plan, elevation, date) and one data field (amount). I am using Excel 2000 on Windows 2000. The row fields have a one-to-one relationship with each other (each code has one description), therefore each code entry occupies only one line. Each plan has three elevation values, and each elevation has two date values. The calculation I am trying to achieve is the difference between the value (amount) of the first date entry and the second date entry for each cost code. What I have tried is to click on the date field in the column area, Formulas:Calculated Item. Then in the calculated item pop-up: click on the date field, click the most recent date and insert item, type a minus sign, then click the old date and insert item. Then click OK. Excel appears to hang at this point. However, I waited long enough (some fifteen plus minutes) and finally excel came back with a solution. However, the output was not what I wanted. The pivot table returned ALL description values for EACH code value in the row area, with the amount value populated only on the correct (corresponding) description line. It also appears to have sorted the descriptions WITHIN each code. The formula line calculates correctly on the one line per code that has values. What did I do wrong? Example output: I hope the formatting is legible. Plan1 Elv1 Code - Desc - Date1 - Date 2 - Formula 1 1D 100 150 50 5D 13D 8D 2D 2 1D 5D 13D 8D 2D 80 120 40 Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Erroneous Grand Total of calculated fields in pivot table | Excel Discussion (Misc queries) | |||
Calculated field in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table | Excel Worksheet Functions | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |