| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
I have a Pivot table summurizing data where years and quarter are in the row
label and average af amount in Values: Values Years Date Average of Amount $2 % diff previous Q 2005 Qtr3 $283.96 Qtr4 $344.83 21.43% 2006 Qtr1 $462.70 Qtr2 $603.55 30.44% Qtr3 $665.53 10.27% Qtr4 $674.41 1.33% 2007 Qtr1 $665.95 I run % difference from (previous), but i can't get say Q1 2006 % Difference from the previus Q (Q4 2005) and the same for every Q1 with respect of the Q4 of previous year, all the rest of course works fine. Any hints? thanks Andrea |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table
Look at your equation for Q1 for each year. I'm betting it's assuming the
"previous quarter" is in the blank row above it. "Andrea" wrote: I have a Pivot table summurizing data where years and quarter are in the row label and average af amount in Values: Values Years Date Average of Amount $2 % diff previous Q 2005 Qtr3 $283.96 Qtr4 $344.83 21.43% 2006 Qtr1 $462.70 Qtr2 $603.55 30.44% Qtr3 $665.53 10.27% Qtr4 $674.41 1.33% 2007 Qtr1 $665.95 I run % difference from (previous), but i can't get say Q1 2006 % Difference from the previus Q (Q4 2005) and the same for every Q1 with respect of the Q4 of previous year, all the rest of course works fine. Any hints? thanks Andrea |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table
yep! but even if i remove the blank row no way!
Consider quarter and years are created automatically grouping a date field like 01/01/2006 (if it can help). i have been around this "bug" for two years and i thought eventualy excel 2007..... but nothing. it seems pivot table consider every years as a block so before Q1 there is nothing.... "Barb Reinhardt" wrote: Look at your equation for Q1 for each year. I'm betting it's assuming the "previous quarter" is in the blank row above it. "Andrea" wrote: I have a Pivot table summurizing data where years and quarter are in the row label and average af amount in Values: Values Years Date Average of Amount $2 % diff previous Q 2005 Qtr3 $283.96 Qtr4 $344.83 21.43% 2006 Qtr1 $462.70 Qtr2 $603.55 30.44% Qtr3 $665.53 10.27% Qtr4 $674.41 1.33% 2007 Qtr1 $665.95 I run % difference from (previous), but i can't get say Q1 2006 % Difference from the previus Q (Q4 2005) and the same for every Q1 with respect of the Q4 of previous year, all the rest of course works fine. Any hints? thanks Andrea |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table
You could add a column to the source data, and calculate the year and
quarter, e.g.: =YEAR(A2) &"-"& VLOOKUP(MONTH(A2),$Q$2:$R$13,2) Add that field to the pivot table, and use it as the base field for the custom calculation. Andrea wrote: yep! but even if i remove the blank row no way! Consider quarter and years are created automatically grouping a date field like 01/01/2006 (if it can help). i have been around this "bug" for two years and i thought eventualy excel 2007..... but nothing. it seems pivot table consider every years as a block so before Q1 there is nothing.... "Barb Reinhardt" wrote: Look at your equation for Q1 for each year. I'm betting it's assuming the "previous quarter" is in the blank row above it. "Andrea" wrote: I have a Pivot table summurizing data where years and quarter are in the row label and average af amount in Values: Values Years Date Average of Amount $2 % diff previous Q 2005 Qtr3 $283.96 Qtr4 $344.83 21.43% 2006 Qtr1 $462.70 Qtr2 $603.55 30.44% Qtr3 $665.53 10.27% Qtr4 $674.41 1.33% 2007 Qtr1 $665.95 I run % difference from (previous), but i can't get say Q1 2006 % Difference from the previus Q (Q4 2005) and the same for every Q1 with respect of the Q4 of previous year, all the rest of course works fine. Any hints? thanks Andrea -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table
yes! this is my work around. But i wonder if there is any way to do it in the
Pivot Table "environment" maybe using VBA. thanks a lot anyway "Debra Dalgleish" wrote: You could add a column to the source data, and calculate the year and quarter, e.g.: =YEAR(A2) &"-"& VLOOKUP(MONTH(A2),$Q$2:$R$13,2) Add that field to the pivot table, and use it as the base field for the custom calculation. Andrea wrote: yep! but even if i remove the blank row no way! Consider quarter and years are created automatically grouping a date field like 01/01/2006 (if it can help). i have been around this "bug" for two years and i thought eventualy excel 2007..... but nothing. it seems pivot table consider every years as a block so before Q1 there is nothing.... "Barb Reinhardt" wrote: Look at your equation for Q1 for each year. I'm betting it's assuming the "previous quarter" is in the blank row above it. "Andrea" wrote: I have a Pivot table summurizing data where years and quarter are in the row label and average af amount in Values: Values Years Date Average of Amount $2 % diff previous Q 2005 Qtr3 $283.96 Qtr4 $344.83 21.43% 2006 Qtr1 $462.70 Qtr2 $603.55 30.44% Qtr3 $665.53 10.27% Qtr4 $674.41 1.33% 2007 Qtr1 $665.95 I run % difference from (previous), but i can't get say Q1 2006 % Difference from the previus Q (Q4 2005) and the same for every Q1 with respect of the Q4 of previous year, all the rest of course works fine. Any hints? thanks Andrea -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| Pivot Table Question : If statment in Pivot Table?? | Excel Discussion (Misc queries) | |||
| Pivot table keeps dupping to another linked pivot table | Excel Discussion (Misc queries) | |||
| pivot table: create worksheetsheet using pivot table | Excel Discussion (Misc queries) | |||
| How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Linear Mode
