Hello,
I have a pivot table set out as below, with the dates grouped by Month, Qtr and Year. The pivot table is linked from 2 data sources
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]QTR4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OCT[/TD]
[TD][/TD]
[TD][/TD]
[TD]NOV[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIFF[/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIFF[/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIF[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]100[/TD]
[TD]110[/TD]
[TD]10%[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]100%[/TD]
[TD]70[/TD]
[TD]50[/TD]
[TD]-29%[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]100%[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]0%[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]-20%
[/TD]
[/TR]
</tbody>[/TABLE]
I am having problems with the % Diff columns. Currently I have set up a column in the original data to work it out, but when I collapse the Qtr or Year, it becomes a sum of the %'s:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]QTR4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIFF[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]220[/TD]
[TD]260[/TD]
[TD]81%[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]70[/TD]
[TD]90[/TD]
[TD]80%[/TD]
[/TR]
</tbody>[/TABLE]
I can get it to work using a calculated field, but only if the pivot table has 1 set of source data. And ideally I would like to have both sources feeding in.
I have a pivot table set out as below, with the dates grouped by Month, Qtr and Year. The pivot table is linked from 2 data sources
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]QTR4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OCT[/TD]
[TD][/TD]
[TD][/TD]
[TD]NOV[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIFF[/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIFF[/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIF[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]100[/TD]
[TD]110[/TD]
[TD]10%[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]100%[/TD]
[TD]70[/TD]
[TD]50[/TD]
[TD]-29%[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]100%[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]0%[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]-20%
[/TD]
[/TR]
</tbody>[/TABLE]
I am having problems with the % Diff columns. Currently I have set up a column in the original data to work it out, but when I collapse the Qtr or Year, it becomes a sum of the %'s:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]QTR4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC[/TD]
[TD]AC[/TD]
[TD]%DIFF[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]220[/TD]
[TD]260[/TD]
[TD]81%[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD]70[/TD]
[TD]90[/TD]
[TD]80%[/TD]
[/TR]
</tbody>[/TABLE]
I can get it to work using a calculated field, but only if the pivot table has 1 set of source data. And ideally I would like to have both sources feeding in.