Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Highlighted
Partner
Partner

TOP 5, FLOP 5 if more than 10 departments, otherwise, normal table.

Hi, 

I'd like to see if I can use a calculated dimension on certain conditions:

if the number of departments in a straight table is more than 10, then display only the TOP & FLOP 5.
Otherwise, just display the departments. 

I'll need to create only 1 object.

Thx for your help guys.

1 Solution

Accepted Solutions
HirisH_V7
Honored Contributor

Re: TOP 5, FLOP 5 if more than 10 departments, otherwise, normal table.

You can create some thing like this in Calculated Dimension.

=Aggr(If(Rank(-Sum(Sales))<=5,Customer,
If(Rank(Sum(Sales))<=5,Customer)),Customer)

 

For the above put a enable conditional ,

=Count(Distinct Customer)>10

Similary complete dimension and put:

=Count(Distinct  Customer)<10

 

Capture.JPG

For ref

 

 

HirisH
“Aspire to Inspire before we Expire!”
2 Replies
Partner
Partner

Re: TOP 5, FLOP 5 if more than 10 departments, otherwise, normal table.

Hi,

create a table,

dimension

=if(aggr(rank(sum(Value),department,1),department)<=5 or aggr(rank(sum(Value),department,1),department)>=$(=count(distinct department)-4),department)

 

create a second dimension

=if(aggr(rank(sum(Value),department,1),department)<=5 ,'top',
if(aggr(rank(sum(Value),department,1),department)>=$(=count(distinct department)-5),'flop'))

there you can see if the department is top or flop

then create your expression

the second dimension you can set show only condition if(count(distinct department) > 10

so it is only shown if there are more tan 10 departments possible.

Regards

HirisH_V7
Honored Contributor

Re: TOP 5, FLOP 5 if more than 10 departments, otherwise, normal table.

You can create some thing like this in Calculated Dimension.

=Aggr(If(Rank(-Sum(Sales))<=5,Customer,
If(Rank(Sum(Sales))<=5,Customer)),Customer)

 

For the above put a enable conditional ,

=Count(Distinct Customer)>10

Similary complete dimension and put:

=Count(Distinct  Customer)<10

 

Capture.JPG

For ref

 

 

HirisH
“Aspire to Inspire before we Expire!”