Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Something like the attached application?
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.
Can you share a sample App or Data.
Have you tried using the following expression
RANK( SUM ( Amount)
with Month as the Dimension and selecting Customer from a list box
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.
try adding an Expression like
AGGR(RANK(SUM(amount)),customer,month)
see attached, is that what you need
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.
Maybe because you have month as the first dimension
try
AGGR(RANK(SUM(amount)),month,customer)
Sorry, didn't work. Now I have all customers ranked 1st for every month.