Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
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

Re: Table - show condition

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
YoussefBelloum
Esteemed Contributor

Re: Table - show condition

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

Re: Table - show condition

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

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Table - show condition

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

Re: Table - show condition

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
YoussefBelloum
Esteemed Contributor

Re: Table - show condition

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

Re: Table - show condition

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

Re: Table - show condition

Don't bother the boss

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Table - show condition

He is resting too much last few days

Highlighted
YoussefBelloum
Esteemed Contributor

Re: Table - show condition

this seems to work Andreas