Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi to everybody,
We have 150 sales reps, and 15 Area manager, (each AM has 10 reps on average).
I need to show the global ranking even selecting a single Area manager:
this is not working:
aggr(
rank( sum( {<AM = >} Sale)))
,AM,SalesRep)
This is showing all the sales Rep even if I select one Aareamanager:
rank( sum( {<AM = >} Sale))
So if this is my fact table:
AM | Reps | Sales | Global Rank |
Luigi | Lucia | 16 | 1 |
Luigi | Alberto | 15 | 2 |
Luigi | Andrea | 12 | 4 |
Luigi | Mario | 7 | 8 |
Mario | Luca | 13 | 3 |
Mario | Antonio | 11 | 5 |
Mario | Giorgio | 10 | 6 |
Mario | Roberta | 9 | 7 |
When select Mario i'll need to have this output:
AM | Reps | Sales | Global Rank |
Mario | Luca | 13 | 3 |
Mario | Antonio | 11 | 5 |
Mario | Giorgio | 10 | 6 |
Mario | Roberta | 9 | 7 |
Thanks in advance!
Best regards,
Nadmar, Thanks for the explanation.
In this case we do not use AGGR by Area Manager, just by your salesperson.
Try this:
=aggr(
rank(
sum({<AM = >} Sales))
,Reps)
The result i got when selecting Mario was:
without selections:
Let me know if it works.
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics
Hi NadMar,
I have loaded your data, and just Selecting Mario without any expressions, gives the expected Output:
If this is not what you want, can you further elaborate your question ?
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics
Hi Matheus,
tnks for the replay!
actually "Global rank" should be a formula, in example i have written is already calculated,
consider that sales will have 10K rows per each rep per month
so i have to sum the sales and ranking it
Nadmar, Thanks for the explanation.
In this case we do not use AGGR by Area Manager, just by your salesperson.
Try this:
=aggr(
rank(
sum({<AM = >} Sales))
,Reps)
The result i got when selecting Mario was:
without selections:
Let me know if it works.
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics
Sorry, i got stuck in a trivial..
Thank you very much!!
can i ask the last advise, so the problem was that i had 2 dimensions, sales rep, and product category, and i was aggregating for the wrong dimension,
now i would like to se the total rank for the total product, i tried to see if i can use dimensionality or rowno to put different formulass (one for the total and the other for the cell values:
So if i agregate it correctly the total is not going to appare, if i take out the product category the formula'll not work inside the cell
i ow you a beer
Hi,
I couldn't understand your question. Can you elaborate it further or give some sample data with the expected output ?
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics