December 10th: Top- and Bottom list in the same table

Creating toplists (and bottom lists) is quite easy in TARGIT. 

A built-in function on the calculation tab allows you to create these automatically.

However, sometimes you might want to take it a step further and look at the outliers in general (top and bottom). 
That can be done with a bit of "magic" with visibility agents.

Let's do a top- and bottom 3 in the same table.

First, we have a table like this showing revenue per product:



Now let's sort them descendingly by clicking the Revenue header a couple of times:



We are going to use visibility agents, to get the job done. Sometimes the issue with visibility agents, is to validate that they removed the desired data (since you can't see it anymore :-))

My favorite technique is to make color agents first and then convert them to visibility agents. That's a good way to validate what will be removed by the condition you set up.

My first color agent says something like this:


That just means - please count the rows from the top and start marking them up when we are past row no 3.

With a color agent with that condition inserted, our table now looks like this (the top of it anyway):



So if we used this as a visibility agent, it would hide every row after row no 3.

So far so good - but what about the bottom?

Let's change the color agent slightly:


Only change is d1 being changed to d-1 as row reference, but it makes a world of difference. Not it counts rows from the bottom, and inserting another coloragent with this syntax, the bottom of the table now looks like this:



So we just need to make both conditions work at the same time - so change the color agent to this:

allcount(d1,d1:0,m1)>3 AND allcount(d1,d-1:0,m1) >3

This means - mark up the ones that comes after row 3 when you count from the top AND also meets the other condition - which is the rows after 3 counting from the bottom.

Scrolling through the table, you can now see that it works as intended - we are now marking up every row (except the 3 in the top and the 3 in the bottom).

Let's do a copy of the table and copy the syntax of the color agent to a visibilty agent, now the new table should look like this:



Now you have the outliers in both ends of the spectrum - top- and bottom list in one object.


Was this article helpful?
1 out of 1 found this helpful


1 comment
  • This is a simple and great Idea.

    I just try to build this with a TOP / FLOP Percentage.

    This is also a standard Functionality to use the TOP or FLOP x Percent.

    But what is the formula behind this and how could this be done?

    I'm exited about your Ideas.


Please sign in to leave a comment.