top # users per month

[#1]

Hello,

I am trying to to create the following chart. Count of transaction for the top 20 users per month.

Dimenstions: Period

Expressions: count({$*<[Responsible Person]={"=rank(count(DISTINCT Trans))<=20"}>}DISTINCT Trans)

The problem I run into is that because the expression selects the overall top 20 people for all the periods selected it counts up the transactions for the whole 4 months.

Open period	Number of Trans
	16432
201104	2740
201102	4282
201101	4328
201103	5082

If we drill down to a month the people selected change, so the values we count also change. Is there a way to show the value below for all months, portraying the top 20 people's total amount of transactions?

Open period	Number of Trans
	4614
201102	4614

[#2]

Hello tupac.alvarez,

when I got you right, you should tell your rank()-function within your expression to rank always monthy (per month or per period). So try to use the aggr() - function within the SET Analysis similar to this:

{"= AGGR (rank(count(DISTINCT Trans)), Period) <=20"}

HtH

Roland

[neetu_singh]

Hi,

Please use this expression on dimension and then add another dimension. Please edit this expression as per your requirement and "VTop" is a variable in which u can define nos of top peoples you want to view. This expression is use to define rank.

=IF(
aggr(
rank(total
aggr(Sum({<Year={"$(=year(Max(TRANSACTION_DATE)))"},Month = {"$(=Month(Max(TRANSACTION_DATE)))"}>} SalesDetail_Amt),CUSTOMER_ID)
,0,1)
,CUSTOMER_ID)
<='$(VTop)',

aggr(
rank(total
aggr(Sum({<Year={"$(=year(Max(TRANSACTION_DATE)))"},Month = {"$(=Month(Max(TRANSACTION_DATE)))"}>} SalesDetail_Amt),CUSTOMER_ID)
,0,1)
,CUSTOMER_ID))

[gauravkhare]

Hi Tupac,

I appreciate all the posts you recieved..Have tried out this simple way..as per your requirment it's better if you can select Month as dimensions and then the count of Users as your expression and then sort those in decending order and also put a limit rather check the box and limit the number to 20. I feel it's an easy way......Well our seniors have told you a sound way towards solving your problem...kindly try out my suggestion also.

[#5]

Hello Gaurav,

yes, this is an easy way to limit the rows to 20. But take care, you are limiting only the rows to be shown , not the rows to be calculated with. To proof what I mean put a total sum to that expression and start playing with different selections. It will always calc the total sum of all (selected) rows, not of the shown rows in the chart.

Regards, Roland