3
1
I have table that has the same account names but for different years (sample below). I want to create a pivot item field that calculates the difference between the years for the same account name
Account Name Year Amount Account 1 2014 15000 Account 1 2015 20000 Account 2 2014 30000 Account 2 2015 60000
So for Account 1 the difference is 5K and coount 2 the difference is 30K. How do i get the difference for the two fields when the amounts belong to the same column and can only be differentiated by year?
Sum of Amount Column Labels
Row Labels 2014 2015 Grand Total Account 1 15000 20000 35000 Account 2 30000 60000 90000 Grand Total 45000 80000 125000The output I would like to show is below:
I need the change to be part of the pivot table because I want to then get the top 40 base on change column.
Sum of Amount Column Labels
Account. 2014 2015 Change Account 2 30000 60000 30000 Account 1 15000 20000 5000 Grand Total 45000 80000 35000
Thanks I've used the "difference from" in the values & did put amount in the values the second time. Values now have change amount, 2014 & 2015 amount.The second problem that I'm having is that (1) "Change" field has is present twice in the values because there are 2 years in columns (2014, 2015). I've tried to hide the change value with no content but the column with change content is getting an error instead of the difference from the 2 years. Second (major), the top 10 values based on "change" amount does not work. Any clue why top 10 does not work? – GC10 – 2015-04-15T16:05:51.210
You can hide the first year Diff by clicking the column heading, right click and hide column. Not perfect but OK. Top-10 seems to work for me. Click in the blank cell ABOVE the heading for the Diff column you want to filter on, right click/filter/top-10 – Julian Knight – 2015-04-15T23:51:53.177
I take back the top-10 comment. It filtered top-10 years not differences! I think the only way to do that is to sort your source data by name/year and then create a new column to calculate the diff. That might be a complex formula if you have missing years for accounts (sparse data). – Julian Knight – 2015-04-16T11:02:21.673