Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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}>} ???? )
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?
Hi,
I feel, we can' do this through Pivot table. But, we can do the same from straight table. See the attachment.
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.
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.
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