How to rank a simple selection

[#1]

I'm sure this is a simple question for  the experienced ones ... but as a bloody beginner to  QV it's costing me an afternoon without success.

From the data in my (simplified) table

<Month> <customer><amount>

I want to plot a chart for each <customer> (i.e. customer in listbox)  and the dimension <month>.

The data in the chart should be sum(amount) and the rank of the (selected) customer (by sum(amount))

However I put the expression, I either get the rankings of all months or no result.

I'm sure I have to work with AGGR but wherever I place it seems to be wrong.

Can anybody help please.

[nagaiank]

Something like the attached application?

[#3]

Dear NagaianK,

thank you for your quick reply.

The application you attached contains the chart for sum(amount) for each (selectable) customer over the dimension month. In this chart I want to add a line that represents the rank of the selected customer by sum(amount) among all customers per month.

In my understanding that means I have to calculate sum(amount) for each customer and month and plot the rank of the result for my selected customer for each month. This is where I have my problem.

Your help would be much appreciated.

[rustyfishbones]

Can you share a sample App or Data.

[rustyfishbones]

Have you tried using the following expression

RANK( SUM ( Amount)

with Month as the Dimension and selecting Customer from a list box

[#6]

Yes, I've tried RANK(Sum(Amount). But then it ranks the months, pls see attached app:

.

On sheet 'dimension month' I have the chart by month with possible selection of customer plottin the amount. In this chart I want to integrate the information how the seleced customer ranks among his peers for each month. I have plotted that on the sheet 'dimension customer' where it shows that customer A is 3rd in month 1, 4th in months 2 and 3 and ranks 1st in month 4. This is what I want to plot additionally in the chart on sheet 'dimension month' for a selected customer ... I just don't know how.

I'm sure I need a set analysis together with AGGR but however I put it I just don't get the right result.

[rustyfishbones]

try adding an Expression like

AGGR(RANK(SUM(amount)),customer,month)

see attached, is that what you need

[#8]

Thank you for the quick reply, the app and your advice

.... unfortunatley the rankings that the expression returns are not correct.

I did a comparison of the results, expected vs expepression:

month	customer	Sum(amount)	Rank expected	AGGR(RANK(SUM(amount), customer, month)
1	B	200	1	2
1	C	150	2	3
1	A	100	3	4
1	D	85	4	4
2	B	180	1	3
2	C	170	2	1-2
2	D	160	3	2
2	A	110	4	3
3	B	220	1	1
3	C	170	2	1-2
3	D	145	3	3
3	A	120	4	2
4	A	210	1	1
4	D	195	2	1
4	C	100	3	4
4	B	65	4	4

As you can see the ranks from the expression are not representing the actual rank and I haven't managed to find out what they rank.

[rustyfishbones]

Maybe because you have month as the first dimension

try

AGGR(RANK(SUM(amount)),month,customer)

[#10]

Sorry, didn't work. Now I have all customers ranked 1st for every month.