topic Re: Rank - considering all the possible dimensions, selecting one by one dimension in App Development

Rank - considering all the possible dimensions, selecting one by one dimension

NadMar — Mon, 12 Sep 2022 13:43:13 GMT

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,

Re: Rank - considering all the possible dimensions, selecting one by one dimension

Zapparoli — Mon, 12 Sep 2022 14:13:28 GMT

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 ?

Re: Rank - considering all the possible dimensions, selecting one by one dimension

NadMar — Mon, 12 Sep 2022 14:41:11 GMT

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

Re: Rank - considering all the possible dimensions, selecting one by one dimension

Zapparoli — Mon, 12 Sep 2022 15:07:04 GMT

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.

Re: Rank - considering all the possible dimensions, selecting one by one dimension

NadMar — Mon, 12 Sep 2022 15:57:20 GMT

Sorry, i got stuck in a trivial..

Thank you very much!!

Re: Rank - considering all the possible dimensions, selecting one by one dimension

NadMar — Mon, 12 Sep 2022 16:33:19 GMT

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

Re: Rank - considering all the possible dimensions, selecting one by one dimension

Zapparoli — Mon, 12 Sep 2022 19:31:32 GMT

Hi,

I couldn't understand your question. Can you elaborate it further or give some sample data with the expected output ?