PIVOT TABLES-PERCENT/AMOUNT OF CHANGE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created 2 pivot table--one for 2006 and one for 2007 as this was the
easiest way to filter through alot of data. My objective is to see how many
new ship to's ordered in 2006 vs. 2007. Any ship to's that have not ordered
show 0 dollar value.

2006 2007
ship to amount ship to amount
321 500 326 250
326 0 321 0
334 250 344 100

I also used a formula in the ship to fields to combine the ship to code with
the city and state so the ship to cells would read 321-greenville, sc.
I want to see how many more ship to's ordered in 2007 vs. 2006. After, I see
the total NUMBER of new ship to's that have ordered, I'd like to be able to
create a percentage.
I'd also like to see how many ship-to's in 2006 have not placed an order in
2007.

Also, my rows are not aligned. I'd like to be able to see ship-to 321 in
2006 next to ship to 321 in 2007. Is there a way to do this. Remember, that
there are some ship to's on the 2006 list that are not on the 2007 and vise
versa.

What are the formulas I can use?
 
Hi

Where is your source data located? Is it in one table or two separate
tables? What is the total number of rows of data?

If the data is all located in a single table, then you can achieve what
you want without any formulae external to the Pivot Table.
Also, you need not have concatenated fields together for ship code, city
and state, adding all three fields to the Row area of the PT would
achieve the same thing.

Post back with some answers, and maybe we can help you.
 
Hi Roger,

Thanks for your help. As you can see I am new to pivot tables. I did have
two pivot tables, one for 2006 and one for 2007, but I merged them together
as suggested into one table. I also see what you mean about not needing a
formula to merge code, city, and state together...thanx!!
Now I have my pivot table set up like this. Also, this will be something I
want to use on a consistent basis. I need to track progress on new business
and I also need to be able to see when existing business is down. (there are
about 1000 rows of data).

ship to code/ship to city/ship to state/order date/invoice total

I need help on 2 things:

How can I see by ship to if the invoice total was 0 in 2006 (order date),
but now is >0 in 2007?

How can I see if the invoice total for 2006 (order date) is less than 2007
and by what percentage?
 
Hi Roger,

I am going to write another question later because after I received your
first answer you made me think about doing this report a whole different way.

Thanks alot for your help and quick response!!
 
Back
Top