Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
motzfeldt01
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
OmarBenSalem
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
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
Partner
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
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.

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

OmarBenSalem
Partner
Partner

Don't bother the boss

YoussefBelloum
Champion
Champion

He is resting too much last few days

YoussefBelloum
Champion
Champion

this seems to work Andreas