Grouping customers per rating

[#1]

Hi all,

I am facing a little problem, but I can't find a solution.

I have a customers table with the key "C_ID" and a linked sales table (including the amount "SaleAmount"), and I dinamically need to calculate a rating per customer using a variable, and it works. What I am not able to do is to show a linear table with grouping per range of rating and showing the count of C_ID. Example:

the variable "ratingExpr" has value "sum(SaleAmount)"

and I show the rating of every customer with $(=$(ratingExpr))

the resulting table should look like this:

rating                                    | count

-----------------------------------------------------

less than 10.000                    | 15

between 10.001 and 20.000    | 28

more of 20.000                       | 9

How can I achieve this? By using Aggr() ? If yes, could you please explain me how to do?

Thank you in advance

Carlo A. Babini

[whiteline · Accepted solution]

Hi.

Have you tried calculated dimension ?

You coul use expression as 'rating' dimension, for example:

=aggr(if(rank(sum(SaleAmount))<10000, 'less than 10.000', 'more than 10.000 or equal') , C_ID)

[#3]

Hi,

I'm sorry but I was not able to try your suggestion before today, but... it just works for me!

Thank you so much, but could you please explain me how does Rank() function works? I tried to undersand the manual page but it is not that easy for me.

Thank you

Carlo A. Babini

[MayilVahanan]

Customer	Sum(Amount)	Rank Value
XXX	200	3
YYY	100	4
ZZZ	1000	2
XYZ	1010	1

Hi

From the above table, you can find , rank function gives the number based on higher value..

So, if we used like this, =if(rank(sum(Amount)) <= 2, 'Less than 3','More than 3')

for customer : zzz,xyz display Less than 3

                       xxx,yyy display More than 3

I think you will understand the concept..

[#5]

Yes, now it is a little clearer

Thank you

Carlo A. Babini