Complex Set Analysis for ranking

[Anonymous]

Hi,

I'm using the rank() function to create a chart that only represents the "5 over / 5 under" values of a particular expression.

For instance :

I've got 39 entities and , for a particular KPI, I want to display :

- The value for 1 entity (that is stored in a variable throughout the whole application in v_CRCA)

- The values of, at most, the expression for the next 5 entities

- The values of, at most, the expression for the previous 5 entities

So, if my main entity is ranked 5th, I want to see it along the entities that are ranked from 10 to 1.

The properties of my graph are :

- Dimensions : Entity | Year

- Expression :

if(

rank(Total Sum({<Entity+={$(v_CRCA)}, Marche=>}[Mtt Sinistres]),4,0)>=

  Only({1<Entity={$(v_CRCA)}>}aggr(rank(Sum({ <Entity=> } [Mtt Sinistres]),1,1),Entity))-5

and

rank(Total Sum({<Entity+={$(v_CRCA)}, Marche=>}[Mtt Sinistres]),4,0)<=

  Only({1<Entity={$(v_CRCA)}>}aggr(rank(Sum({ <Entity=> } [Mtt Sinistres],1,1),Entity)))+5

,

     Sum({<Entity+={$(v_CRCA)}, Marche=>}[Mtt Sinistres])

,

     Null()

)

I've got 2 possible years (N & N-1) and it goes fine if I select just 1 year. But if I select 2 years, It gets lost in the rankings. (I want the ranking to be based on the last year only)

I've attached 2 pics to show the difference (my final representation will be a bar chart).

I'd like to have the same ranking value for N & N-1 (N's ranking)

Thanks in advance

[sunny_talwar]

Would you be able to share a sample to look at what you have?