Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
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?

22 Replies
Highlighted
Partner
Partner

Hi Omar,

It works, thank you. But I forgot to mention, that I also have a filter on the dimension: [Indsutry]. Thus the rank are based on each industry in the dataset.

Can I add a set analysis?

Like this:

{$<Industry={$(vChosenIndustry)}>}

Highlighted
Partner
Partner

Try to create a table source as u've done in ur first post; It would be easier to help once you exactly describe the desired output with a given input.

Ps: You can mark the given responses as helpful if they were

Highlighted
Partner
Partner

Of course. Here are the detailed version:

In my qlik app, I have from a filter chosen Company E.

This gives the following table.

CompanyRankGross Profit
A1100$
B298$
C393$
D490$
E587$
F684$
G782$
H877$
I966$
J1062$

All 10 companies are within the same industry.

Rank are made by this expression:

=Rank(Sum({<[Company]=,

Indsutry={$(vChosenIndustry)},

[Year] = {$(vThisYear)}>}

[Gross profit]))

Gross profit are made by this expression:

=Sum({$<[Company]=,

[Year]={$(vThisYear)},

Industry={$(vChosenIndustry)}>}

[Gross profit]

But I want Qlik to give me this table:

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

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

Let me know if I need to elaborate further 🙂

Thanks in advance!

Highlighted
Master II
Master II

Hi Omar,

Nice solution, can you please help me to understand the below syntax ?

rank(sum({1}Gross),Company) --> How are you using Company as a parameter, and how does it work ?

Thanks,

KC

Best Regards,
KC
Highlighted
Partner
Partner

This rank the Companies based on their sum(Gross)

Company with the most sum(Gross) will be number 1 etc..

Highlighted
Master II
Master II

Thanks for the explanation ...

Is it same to .....

Aggr(rank(sum({1}Gross)),Company) ?


Br,

KC

Best Regards,
KC
Highlighted
Partner
Partner

Is it possible omarbensalem‌? 🙂

Highlighted
Partner
Partner

it's : Aggr(rank(sum({1}Gross)),Company) ,Company)


and yes, it's the same thing; because u can rank the companies based on its industry for example; this is why I want to introduce the aggr() notion;

in that case (ank the companies based on their industry), u should tell Qlik to do so with the aggr():

Aggr(rank(sum({1}Gross)),Company) ,Industry,Company)


This translate to :

rank the companies based on the sum(Gross) but rank them on their own Industry


here's an explanation by example:

Capture.PNG

Highlighted
Partner
Partner

Do you mean like this?

if(getselectedcount([Company])=0 and (Industry)=0,sum([Gross Profit]),


sum({<[Company]=

{"=rank(Aggr(sum({1}[Gross Profit]),[Company]), Industry, [Company])<=max(total aggr(rank(sum({<[Company]>}[Gross Profit]),[Company]),Industry, [Company]))+3"}>


*


<[Company]=

{"=rank(Aggr(sum({1}[Gross Profit]),[Company]), Industry, [Company])>=max(total aggr(rank(sum({<[Company]>}[Gross Profit]),[Company]),Industry, [Company]))-3"}>


}[Gross Profit])


)

Doesn't seem to work.

Highlighted
Partner
Partner

try this: ( youssefbelloum‌ , this might interest u)

if(GetSelectedCount(Company)=0,sum(Gross),

sum({

<Industry=p(Industry),Company={"=sum({1}aggr({1}rank(sum({1}Gross),Company),Industry,Company))<=max({<Industry=p(Industry)>}total <Industry> aggr({1}rank(sum({1}Gross),Company),Industry, Company))+1"}>

*

<Industry=p(Industry),Company={"=sum({1}aggr({1}rank(sum({1}Gross),Company),Industry,Company))>=max({<Industry=p(Industry)>}total <Industry> aggr({1}rank(sum({1}Gross),Company),Industry, Company))-1"}>

}

Gross)

)

Suppose this is what you have;

Company, Gross, Industry

K, 120, Ind1

A,100, Ind1

B,98, Ind1

C,93,Ind2

D,90,Ind1

L, 80, Ind1

E,87,Ind2

F,84,Ind2

G,82,Ind2

Capture.PNG

If I select Company B; I want to show A,B and D, the one before and after it in rank in the Ind1:

Result:

Capture.PNG

If I select E :

Capture.PNG