I have two questions I was hoping I could get some help from the experts here. I have unit sales data for the whole country that I add to my raw data file monthly as additional rows, see below. My pivot table is linked to the columns of the raw data so it automatically adds each months unit sales to the pivot table. This makes it pretty seamless and easy to update, but I'm lacking some analysis tools that would update automatically with the pivot table.
1) How can I add some functionality to my pivot table to alert me to when a customer's latest month is above or below their average month? ie in column T as a % or using conditional formatting
2) We are growing quickly so I need a tool to analyze how many new customers each product has in a month. Can I put a conditional formatting rule that looks at each customer's preceding monthly sales, if there is nothing there it highlights the 1st sale in green or something?
Or, is there another way to create a pivot table that would count the number of new customers per month, keeping in mind that some customers order every month and others only order once every 6 months?
Really appreciate any help!
Raw Data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD]Rep[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2012[/TD]
[TD]Account 2[/TD]
[TD]XYZ[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2012[/TD]
[TD]Account 5[/TD]
[TD]XYZ[/TD]
[TD]12[/TD]
[TD]Ryan[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]2013[/TD]
[TD]Account 2[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]2012[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65"]Account 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]XYZ[/TD]
[TD]18[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]2012[/TD]
[TD]Account 3[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]2012[/TD]
[TD]Account 5[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]Ryan[/TD]
[/TR]
</tbody>[/TABLE]
Current pivot table:
Capture pivot table | Flickr - Photo Sharing!
1) How can I add some functionality to my pivot table to alert me to when a customer's latest month is above or below their average month? ie in column T as a % or using conditional formatting
2) We are growing quickly so I need a tool to analyze how many new customers each product has in a month. Can I put a conditional formatting rule that looks at each customer's preceding monthly sales, if there is nothing there it highlights the 1st sale in green or something?
Or, is there another way to create a pivot table that would count the number of new customers per month, keeping in mind that some customers order every month and others only order once every 6 months?
Really appreciate any help!
Raw Data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD]Rep[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2012[/TD]
[TD]Account 2[/TD]
[TD]XYZ[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2012[/TD]
[TD]Account 5[/TD]
[TD]XYZ[/TD]
[TD]12[/TD]
[TD]Ryan[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]2013[/TD]
[TD]Account 2[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]2012[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65"]Account 6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]XYZ[/TD]
[TD]18[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]2012[/TD]
[TD]Account 3[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]2012[/TD]
[TD]Account 5[/TD]
[TD]ABC[/TD]
[TD]6[/TD]
[TD]Ryan[/TD]
[/TR]
</tbody>[/TABLE]
Current pivot table:
Capture pivot table | Flickr - Photo Sharing!
