help on categorizing sales data

**piyushvarade** · 06-17-2014, 04:19 AM

i have a sales report data which shows ordering dates of various companies over a period of 1 year.
i wish to categorize the companies into 3:

if the company orders from us in gaps of :

1) less than a month= categoryA [i.e. a frequent buyer]
2) 2,3,4 months= categoryB
3) more than 4 months= categoryC

how to achieve the results?
either in excel sheet or pivot chart, both are fine, such that in-front of that company its Category is displayed.
i can email you the data file if required, just send me a request email on below address

thank you and regards,

Piyush

Moderator's note: it is not wise to include personal info in public forums

**cbatrody** · 06-17-2014, 04:22 AM

Hi,

Welcome to the Forum.

Please post a sample template of your workbook (excel format) using "Go Advanced" option below. You may choose to delete confidential data in the file, if any.

**cbatrody** · 06-18-2014, 01:51 AM

Hi,

Welcome to the Forum.

Please post a sample template of your workbook (excel format) using "Go Advanced" option below. You may choose to delete confidential data in the file, if any.

**piyushvarade** · 06-18-2014, 02:11 AM

this is the sample sales report

**cbatrody** · 06-18-2014, 02:58 AM

Hi,

I have created a Pivot Table in the attached file. Please see if it suits your requirement.

I have taken yearly average to categorize the customers, i.e, total orders / no of months (12).

**piyushvarade** · 06-18-2014, 03:13 AM

Hello,

The pivot seems excellent. Thank you very much.
I'll have to check with my senior if it is what he expects. I will revert you then.

Meanwhile, can you explain me:

1) how to apply settings of "first order date", "last order date", "total orders", "avg/month"; i could not find them on clicking 'value field settings' in pivot?

2) on what basis did you apply the formula for "category" [more than 1, more than 0.3) ?

**cbatrody** · 06-18-2014, 03:22 AM

1)
First Order Date - MIN of Date Field
Last Order Date - MAX of date Field
Total Orders - SUM of count field
Avg/Month - is a calculated field - COUNT/12

2) I have just categorized it based on "Average Orders/Month" which gives the monthly average.

**piyushvarade** · 06-18-2014, 08:30 AM

Hi,

thank you for the explaination.
i'm unable to replicate the last column of category.
it is giving #VALUE! as an error, when i enter your formula in the 'fields,items & sets---> calculated field' to create a new field of Category.

help on categorizing sales data

LinkBack

Thread Tools

Rate This Thread

Display

help on categorizing sales data

Re: help on categorizing sales data

Re: help on categorizing sales data

Re: help on categorizing sales data

Re: help on categorizing sales data

Re: help on categorizing sales data

Re: help on categorizing sales data

Re: help on categorizing sales data

Thread Information

Users Browsing this Thread

Similar Threads

Categorizing a data set

[SOLVED] Help with categorizing my data

[SOLVED] Categorizing data based on color, then split into columns?

Excel 2007 : Issue with drop-down menus finding/categorizing data

Categorizing and sorting data

Bookmarks

Bookmarks

Posting Permissions