How to class the results of aggregation?

[#1]

For example, I have a table that looks like this:

Date	Sales Person	Sales
1/4/2014	Thomas	3
2/4/2014	Mike	2
2/4/2014	Adam	1
2/4/2014	Thomas	2
3/4/2014	Mike	1
3/4/2014	Kate	5
4/4/2014	Adam	2
4/4/2014	Thomas	5

I would like to classify the sales person based on their total sales performance, i.e.

Sales Performance Class	Number of Sales Persons	Total Sales
<5	2	6
5 - 9	1	5
>=10	1	10

What formula should I use as the dimension for the classification?

I have tried =class(aggr(distinct sum(Sales),SalesPerson),5) but it does not work, nothing came up.

[bbi_mba_76]

Hi,

I tried your function and seems to work, see the attached file

there is a missing space in the aggr [sales person]

[#3]

Thanks for helping @Michele Barini. There was a typo error on 'sales person' above actually. I think the formula works for this simple example.

However the actual data I'm dealing is slightly more complicated which involves a key, which will be linked to two other tables.

Example:

Table 1: Sales Table

Key	Sales
Thomas_1/4/2014	3
Mike_2/4/2014	2
Adam_2/4/2014	1
Thomas_2/4/2014	2
Mike_3/4/2014	1
Kate_3/4/2014	5
Adam_4/4/2014	2
Thomas_4/4/2014	5

Table 2: Purchase Table

Key	Purchases
Thomas_1/4/2014	2
Mike_2/4/2014	1
Adam_2/4/2014	4
Thomas_2/4/2014	5
Mike_3/4/2014	4
Kate_3/4/2014	7
Adam_4/4/2014	3
Thomas_4/4/2014	7

Table 3: Key Table

Key	Date	SalesPerson
Thomas_1/4/2014	1/4/2014	Thomas
Mike_2/4/2014	2/4/2014	Mike
Adam_2/4/2014	2/4/2014	Adam
Thomas_2/4/2014	2/4/2014	Thomas
Mike_3/4/2014	3/4/2014	Mike
Kate_3/4/2014	3/4/2014	Kate
Adam_4/4/2014	4/4/2014	Adam
Thomas_4/4/2014	4/4/2014	Thomas

For this question we will not involve Table 2, I'm showing it to demonstrate all the relationships.

In this case, how should the formula be like?

[MK_QSL · Accepted solution]

Check Enclosed File...!!!

[#5]

Manish Kachhia Thank you so much! This solves my question.

=DUAL(Replace(Aggr(Class(SUM(Sales),5),SalesPerson),'<= x <', '-'),Aggr(Class(SUM(Sales),5),SalesPerson))

Also is there anyway to limit the minimum/maximum value for the class, i.e. such that anything larger than 10 will be in one class?

[MK_QSL]

Possible... Just change the Calculated Dimension as below

=DUAL(

  IF(Aggr(SUM(Sales),SalesPerson)<5, '<5',

  IF(Aggr(SUM(Sales),SalesPerson)>=5 and Aggr(SUM(Sales),SalesPerson)< 9 , '5-9',

  '10+')),

  IF(Aggr(SUM(Sales),SalesPerson)<5, 1,

  IF(Aggr(SUM(Sales),SalesPerson)>=5 and Aggr(SUM(Sales),SalesPerson)< 9 , 2,

  3)))