Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I’m trying to create a chart where if a user selects a client, he can compare it’s sales to the #1 Ranked Client, #5 Ranked Client, and #10 Ranked Client Overall .
Here’s an example of the source data. Now keep in mind the Rank columns do not exist, I've added them in there as reference.
Client Name | Sales (Sum YTD) of Product A | Rank of Sales of Product A (not within the database) | Sales (Sum YTD) Product B | Rank of Sales of Product B (not within the database) |
Client A | 100 | 4 | 25 | 6 |
Client B | 80 | 7 | 50 | 2 |
Client C | 110 | 3 | 1 | 10 |
Client D | 120 | 2 | 30 | 5 |
Client E | 75 | 8 | 38 | 4 |
Client F | 5 | 9 | 22 | 7 |
Client G | 90 | 6 | 100 | 1 |
Client H | 91 | 5 | 5 | 9 |
Client I | 255 | 1 | 60 | 3 |
Client J | 3 | 10 | 15 | 8 |
Now if a user selects Client A, the chart I’m aiming to create is the following:
Products | Client A (Parameter Driven) | Client Ranked Sales #1 | Client Ranked Sales #5 | Client Ranked Sales #10 |
Product A | 100 | 255 | 91 | 3 |
Product B | 25 | 100 | 30 | 1 |
Now the columns I’ve highlighted in yellow are the ones I’m having a hard time creating expressions for. Does anyone know of a way to do this using AGGR/Rank/Sum or another expression?
Hi,
I've taken a guess at your data structure but the attached should do it.
Hope this helps,
Jason
Thanks Jason for the quick reply. I looked it over, and I think the returned ranked values in your application are based on the total of the two.
Based on your data, I believe the ranked 1 and 5 values should show:
Product | A | B | ||
Client Name | Sales | Rank | Sales | Rank |
Client A | 664 | 7 | 236 | 7 |
Client B | 559 | 8 | 1434 | 1 |
Client C | 1396 | 1 | 0 | 10 |
Client D | 305 | 9 | 790 | 5 |
Client E | 846 | 6 | 1181 | 2 |
Client F | 970 | 5 | 995 | 3 |
Client G | 1139 | 3 | 854 | 4 |
Client H | 32 | 10 | 177 | 8 |
Client I | 1120 | 4 | 441 | 6 |
Client J | 1388 | 2 | 83 | 9 |
Total | 8419 |
| 6191 |
|
Yes, the advanced search will return the customer which is ranked over his total sales.
You can add a restriction to the advanced search set expression, like
SUM({<[Client Name]={"=Rank(SUM({1<Product={A}>}Sales))=1"} >}Sales)
But this you would need to state the correct Product depending on your chart dimension (if you want to create a dynamic table with a lot of products.
Unfortunately, the set expression won't take the current dimension value into account, though there are methods to create a pick(match() ) combination, picking different set analysis expressions depending on the dimension value.
But you could also try this:
=sum({1} aggr(if(rank(sum({1} Sales))=1,sum({1} Sales)),Product,[Client Name]))
See also attached.
Hope this helps,
Stefan