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

[thierrytt1102]

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.

[martinpohl]

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 · Accepted solution]

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

For ref