|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 12:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com