Hello and thanks in advance for your help.
I have a data set and pivot table that contains profit numbers over multiple years. I'm trying to get the report to display not only the profit numbers for 2 years, but also the year over year change - both in dollars and percent.
I'm basically dumping the profit field three times - the first time just sums the field, the second time shows the field as "Difference From" the last year number, and the third time shows the field as "% Difference From" the last year number.
The first two are no problem, but I can't get the third (% change year over year) to calculate properly all of the time - specifically when the last year number is negative (a loss). The % Difference From option divides the difference by the base field so since the base field is negative, the difference is "reversed" - losing more money shows as a positive % improvement since a negative divided by a negative is positive, and losing less money shows as a negative % since a positive divided by a negative is negative.
I've tried many variations of calculated fields and showing values differently but I can't get this to work. Any ideas?
I'm including a small example to illustrate:
Notice how the % change for city C is negative despite the fact that they improved - lost less money - and vice-versa for City D.
Thanks for any suggestions.
joe
I have a data set and pivot table that contains profit numbers over multiple years. I'm trying to get the report to display not only the profit numbers for 2 years, but also the year over year change - both in dollars and percent.
I'm basically dumping the profit field three times - the first time just sums the field, the second time shows the field as "Difference From" the last year number, and the third time shows the field as "% Difference From" the last year number.
The first two are no problem, but I can't get the third (% change year over year) to calculate properly all of the time - specifically when the last year number is negative (a loss). The % Difference From option divides the difference by the base field so since the base field is negative, the difference is "reversed" - losing more money shows as a positive % improvement since a negative divided by a negative is positive, and losing less money shows as a negative % since a positive divided by a negative is negative.
I've tried many variations of calculated fields and showing values differently but I can't get this to work. Any ideas?
I'm including a small example to illustrate:
Code:
[TABLE="width: 159"]
<tbody>[TR]
[TD="colspan: 2"]DATA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]City[/TD]
[TD]Year[/TD]
[TD]Profit[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]This Year[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]This Year[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]This Year[/TD]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]This Year[/TD]
[TD="align: right"]-100[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Last Year[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Last Year[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Last Year[/TD]
[TD="align: right"]-75[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Last Year[/TD]
[TD="align: right"]-70[/TD]
[/TR]
</tbody>[/TABLE]
Code:
[TABLE="width: 503"]
<tbody>[TR]
[TD]Pivot Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Sum of Profit[/TD]
[TD="colspan: 2"]Sum of Profit2[/TD]
[TD="colspan: 2"]Sum of Profit3[/TD]
[/TR]
[TR]
[TD]City[/TD]
[TD]This Year[/TD]
[TD]Last Year[/TD]
[TD]This Year[/TD]
[TD]Last Year[/TD]
[TD]This Year[/TD]
[TD]Last Year[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 60[/TD]
[TD] 70[/TD]
[TD] (10)[/TD]
[TD][/TD]
[TD="align: right"]-14.29%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] 100[/TD]
[TD] 80[/TD]
[TD] 20[/TD]
[TD][/TD]
[TD="align: right"]25.00%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] (50)[/TD]
[TD] (75)[/TD]
[TD] 25[/TD]
[TD][/TD]
[TD="align: right"]-33.33%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] (100)[/TD]
[TD] (70)[/TD]
[TD] (30)[/TD]
[TD][/TD]
[TD="align: right"]42.86%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Notice how the % change for city C is negative despite the fact that they improved - lost less money - and vice-versa for City D.
Thanks for any suggestions.
joe