Table - show condition

[motzfeldt01]

Hi,

I have made this table:

Company	Rank	Gross Profit
A	1	100$
B	2	98$
C	3	93$
D	4	90$
E	5	87$
F	6	84$
G	7	82$
H	8	77$

And a filter called "ChosenCompany".

I would like to make a condition, thus the table are only showing:

- the three companies above the chosen company based on rank

- the three companies below the chosen company based on rank

- and the chosen company

Does anyone have an idea how to do this?

[YoussefBelloum]

Hi,

if the Rank column is already calculated, this will help you:

=sum({<Company=, Rank={">=$(=Max(Rank)-3)<=$(=Max(Rank)+3)"}>}[Gross Profit])

[motzfeldt01]

Hi, Rank is not a field, but a calculation. How should I take that into account?

[YoussefBelloum]

we store calculations on fields.. what I wanted to ask is: is Rank calculation already calculated on the script ? or it is a calculation you want to do ?

[motzfeldt01]

Hi,

Unfortunately I can't use the script, because the app has to be integrated in my company's system.

I have made a measure in the table using the following code:

Rank(TOTAL SUM({$<[Company]=,

[XBRL Reporting year] = {$(vXBRLThisYear)},

industry={$(vChosenIndustry)}>} [GrossProfit]))

And I need an expression that returns me the chosen company alongside with the three companies above and below based on rank.

[YoussefBelloum]

ok, I got you but I tried everything.. I'm making something wrong on my tests..

let's see if stalwar1‌ can give you the solution here

Hi Sunny, he needs a set expression with something like Gross_margin={">= (rank of the dimension selected- 3) <=(ank of the dimension selected-+3)"}

I'm missing something on the syntax..

[OmarBenSalem · Accepted solution]

Use this expression:

if(getselectedcount(Company)=0,sum(Gross),

sum({<Company={"=rank(sum({1}Gross),Company)<=max(total aggr(rank(sum({<Company>}Gross),Company),Company))+3"}>

*

<Company={"=rank(sum({1}Gross),Company)>=max(total aggr(rank(sum({<Company>}Gross),Company),Company))-3"}>

}Gross)

)

result:

If I select E:

If I select 😧

Hope this helps

[OmarBenSalem]

Don't bother the boss

[YoussefBelloum]

He is resting too much last few days

[YoussefBelloum]

this seems to work Andreas