Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
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.

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