Pivot - Cross Table - Percentage of specific columns

[#1]

Hello everyone,

Need help. I have the following pivot table presented in a CrossTable format:

Code	Year	2010	2011	2012
A00		100	120	150
A01		200	180	200
Total		300	300	350

I want to create a calculated filed (expression) which calculates difference between 2012 / 2010 in percentage. The pivot table should look like this:

Code	Year	2010	2011	2012	%Change 2012 / 2010
A00		100	120	150	50%
A01		200	180	200	0%
Total		300	300	350	16%

How can I do this?

Thank you.

[robert99]

I don't think you can do it in this way

One option would be to use more than one expression (in either a pivot or straight table) using set analysis (such as)

dimension   = Code

and

expression 1  (2010)    Count ({<Year = {2010}>} ???? )

expression 2 (2011)    Count ({<Year = {2011}>} ???? )

expression 3  (2012)   Count ({<Year = {2012}>} ???? )

expression 4  (%) with number formatted as % 

(Count ({<Year = {2012}>} ???? ) -  Count ({<Year = {2010}>} ???? ))      / Count ({<Year = {2010}>} ???? )

[#3]

Thanks RJ. I think this means a mess if number of years increase, and if I want to carry out variances between specific years. Any other suggestion?

[#4]

Hi,

I feel, we can' do this through Pivot table. But, we can do the same from straight table. See the attachment.

[#5]

Hi,

In general, we do compare data between the current year Vs last year (Or) Current Year Vs Past years.

If this is the case, then use the expression like below:

Sum({<Year = {'$(=Max(Year))'}>}Sales)

Sum({<Year = {'$(=Max(Year)-1)'}>}Sales)

Sum({<Year = {'$(=Max(Year)-2)'}>}Sales)

Expression for %Change is Same as mentioned in my last attachment.

[#6]

Thanks Venu / RJ.

I think QV needs to come up with some easy way to accomplish this in a crosstab pivot table. Variances between years, months, is a normal management reporting activity seen from various crosstab dimensions.

[robert99]

Another option is to use variables (setting variable overview etc)

You can for example have variables in set analysis

sum( {$<Year = {$(#vYear1)}>} Sales )

sum( {$<Year = {$(#vYear2)}>} Sales )

sum( {$<Year = {$(#vYear3)}>} Sales )

and use an input box to change the variables (Years) as required. (although I have not done this) You can set up the required variables from the input box