Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Register for Events and Webinars directly from Qlik Community: http://bit.ly/2Vpnenx
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
Highlighted
MVP
MVP

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

Check Enclosed File...!!!

View solution in original post

5 Replies
Highlighted
Partner
Partner

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]

Highlighted
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?

Highlighted
MVP
MVP

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

Check Enclosed File...!!!

View solution in original post

Highlighted
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?

Highlighted
MVP
MVP

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