Product Analysis in Pivot Tables

Originally Posted by pensworth

Hi All,

I have created a Pivot table as below:

Milk 1 Pint Whole

JAN FEB MARCH APRIL etc
Shop 1 10 12 14 20
Shop 2 8 7 6 2
Shop 3 10 10 10 10
Shop 4 20 30 40 50

Although i can manually look at the sales figures and work out if they are buying less or more products i would like a a more automatic way of finding out if customers sales are going down on the up or staying constant. Does anymore have any ideas on how best to acheive this.

Many Thanks

Mike

Mike,

Have you tried using the Pivot Chart option, this would give you a visual representation of your sales by month?

If you put the 'Shops' into the page field, you could then toggle by each shop to see if there sales were incresing or decreasing, you can also add a trend line which will tell you if sales are on the up or are going down.

SJ

Hi SJ,

Many thanks for taking the time to reply. I have trreied this mehtod as as you have suggested it works fine but the only downside being that we have 1300 customers so finding out the trends is quite time consuming. I was hoping that i might be able to use some kind of formula to rate each customer almost by a value and then apply a condition format of say the traficlights, green for customer that are doing fine, red for customers that i need to check into etc.

Regards

Mike

Mike,

This is a little confusing now, are you referring to 1300 Shops?

I was assuming you wanted to review sales trends per each shop to determine if product sales were trending up or down.

If this is not the case then please be more specific on what it is exactly that you are trying to achieve?

SJ

I pivoted your raw data, adding the shops as row items. I then went to pivot table-> formulas -> calculated fields and added a field, which I called Jan-Feb, the formula was =(JAN-FEB)/JAN. When I then sort this field A-Z I see that shop 4 lost 50% from Jan-Feb and shop 1 20% whilst shop 3 was flat month-on-month and shop 2 increased 13%.

You could do similar for each consecutive month pair, or you could look at similar sums over larger date ranges - ((JAN+FEB+MAR)-(APR+MAY+JUN))/(JAN+FEB+MAR) perhaps?

HTH

Hi Charlie,
Many thanks for your input. I have taken on board what you have said and have managed to produce the pivot chart please seea attachment. i have grouped the dates via years and quarter and I have seleted the qty data to show as a percentage of the total row and the applied a condition format to make the finding a little easier to see at a glance.

Regards

Mike

Mike,

Don't forget to mark your thread as Solved if you have found the answer.

Do this by going to your first post in the thread, clicking the Edit Button, then clicking the Advanced Button, then changing the Prefix to read Solved and click the Save button.

Hi Mike,

That looks 2007-tastic!
I didn't know the layout of your source data, so in many ways we may be doing this a slightly odd way (pivot from a pivot). That said, I'm a believer in "if it ain't broke don't fix it" and think fancy solutions are all well and good unless a simple solution will cover it...
If you do get stuck, you may want to post a sample of your original raw data (ideally in backwards compatibility for xl03!) as we may be able to simplify things by adjusting formulas in the first pivot table.

CC