# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for
Did you mean:
Partner

## Table - show condition

Hi,

CompanyRankGross Profit
A1100\$
B298\$
C393\$
D490\$
E587\$
F684\$
G782\$
H877\$

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?

1 Solution

Accepted Solutions
Partner

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

22 Replies
Champion

Hi,

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

Partner
Author

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

Champion

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 ?

Partner
Author

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.

Champion

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..

Partner

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

Partner

Don't bother the boss

Champion

He is resting too much last few days

Champion

this seems to work Andreas