Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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/2014Adam1
2/4/2014Thomas2
3/4/2014Mike1
3/4/2014Kate5
4/4/2014Adam2
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

Re: Re: Re: How to class the results of aggregation?

Check Enclosed File...!!!

5 Replies
bbi_mba_76
Valued Contributor

Re: How to class the results of aggregation?

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

Re: Re: How to class the results of aggregation?

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
Adam_2/4/20141
Thomas_2/4/20142
Mike_3/4/20141
Kate_3/4/20145
Adam_4/4/20142
Thomas_4/4/20145

Table 2: Purchase Table

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

Table 3: Key Table

KeyDateSalesPerson
Thomas_1/4/20141/4/2014Thomas
Mike_2/4/20142/4/2014Mike
Adam_2/4/20142/4/2014Adam
Thomas_2/4/20142/4/2014Thomas
Mike_3/4/20143/4/2014Mike
Kate_3/4/20143/4/2014Kate
Adam_4/4/20144/4/2014Adam
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?

Re: Re: Re: How to class the results of aggregation?

Check Enclosed File...!!!

Not applicable

Re: How to class the results of aggregation?

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?

Re: How to class the results of aggregation?

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