about rank and magic

[#1]

Hi,

i've tried to find a post regarding my issue but haven't found it. So here is it :

i've sales for different products by client.

Let's proceed by steps :

1 - I want to get that kind of array when selecting one client :

PRODUCT A       36

PRODUCT B        2

PRODCUT C       10

That selected client is ranked #36. There're 35 other clients better that him.

That same client is a very good one on my PRODCUT B.

2 - Imagine we have a hierarchy  PRODUCT >> SUBPRODUCT, could my rankings be dynamic

is it possible to drill down into PRODUCT B, to see that kind of ranks :

PRODUCT B1    2

PRODUCT B2    1

PRODUCT B3    5

3 - Cherry on the cake : let's figure that we want to split these ranks by a time hierarchy

                            2014       2015      (year)

PRODUCT A        42            32

PRODUCT B          2              2

PRODCUT C          8            11

and if i want to zoom by month :

                            2014       jan 2015   fev 2015   mar 2015  etc..

PRODUCT A        42            

PRODUCT B          2            

PRODCUT C          8           

I guess the formula is going to be horribly complex.. no magic there.. 

You can just answer my step1 question, i'll be happy !!

Thanks

[sinanozdemir]

Well. If you can provide more elaborated sample data, it would be better. Because you are talking about clients, but you don't have client dimension in your sample so someone provides you an answer, but it may not be applicable to your problem. 

[marcus_sommer · Accepted solution]

You could use a pivot-chart with cyclic- or drill-dimension-groups for both vertically and horizontally dimensions and then should be an expression like rank(sum(value)) enough.

- Marcus

[#4]

Hi and sorry for the delay, i was off

Thank you 2

The rank function does work perfectly when we use it cleverly...

Sorry & thanks again