Announcements
cancel
Showing results for
Did you mean:
Not applicable

## How to class the results of aggregation?

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

DateSales PersonSales
1/4/2014Thomas3
2/4/2014Mike2
2/4/2014Thomas2
3/4/2014Mike1
3/4/2014Kate5
4/4/2014Thomas5

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

Sales Performance ClassNumber of Sales PersonsTotal Sales
<526
5 - 915
>=10110

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.

1 Solution

Accepted Solutions
MVP

Check Enclosed File...!!!

5 Replies
Partner - Specialist

Hi,

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

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

Not applicable
Author

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

KeySales
Thomas_1/4/20143
Mike_2/4/20142
Thomas_2/4/20142
Mike_3/4/20141
Kate_3/4/20145
Thomas_4/4/20145

Table 2: Purchase Table

KeyPurchases
Thomas_1/4/20142
Mike_2/4/20141
Thomas_2/4/20145
Mike_3/4/20144
Kate_3/4/20147
Thomas_4/4/20147

Table 3: Key Table

KeyDateSalesPerson
Thomas_1/4/20141/4/2014Thomas
Mike_2/4/20142/4/2014Mike
Thomas_2/4/20142/4/2014Thomas
Mike_3/4/20143/4/2014Mike
Kate_3/4/20143/4/2014Kate
Thomas_4/4/20144/4/2014Thomas

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?

MVP

Check Enclosed File...!!!

Not applicable
Author

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?

MVP

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)))

Community Browser