Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Table - show condition

Hi,

I have made this table:

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
Highlighted
Partner
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:

Capture.PNG

If I select E:

Capture.PNG

If I select 😧

Capture.PNG

Hope this helps

View solution in original post

22 Replies
Highlighted
Champion
Champion

Hi,

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

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

Highlighted
Partner
Partner

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

Highlighted
Champion
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 ?

Highlighted
Partner
Partner

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.

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

Highlighted
Partner
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:

Capture.PNG

If I select E:

Capture.PNG

If I select 😧

Capture.PNG

Hope this helps

View solution in original post

Highlighted
Partner
Partner

Don't bother the boss

Highlighted
Champion
Champion

He is resting too much last few days

Highlighted
Champion
Champion

this seems to work Andreas