topic Re: Grouping customers per rating in QlikView

Grouping customers per rating

Thu, 18 Oct 2012 16:55:32 GMT

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

Re: Grouping customers per rating

whiteline — Thu, 18 Oct 2012 17:14:42 GMT

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)

Re: Grouping customers per rating

Wed, 24 Oct 2012 19:39:11 GMT

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

Re: Grouping customers per rating

MayilVahanan — Wed, 24 Oct 2012 19:55:55 GMT

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

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

Re: Grouping customers per rating

Thu, 25 Oct 2012 12:32:23 GMT

Yes, now it is a little clearer

Thank you

Carlo A. Babini