Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
motzfeldt01
Partner - Contributor II
Partner - Contributor II

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
OmarBenSalem

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
YoussefBelloum
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])

motzfeldt01
Partner - Contributor II
Partner - Contributor II
Author

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

YoussefBelloum
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 ?

motzfeldt01
Partner - Contributor II
Partner - Contributor II
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.

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

OmarBenSalem

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

OmarBenSalem

Don't bother the boss

YoussefBelloum
Champion
Champion

He is resting too much last few days

YoussefBelloum
Champion
Champion

this seems to work Andreas