When you make a top list in TARGIT, you have at least two options:
- Using the built-in top list function
- Creating your top list with a visibility agent
Here's a table to illustrate, showing revenue by salesperson:
Let's first try the built-in easy way (Method no. 1)
Make sure your table is selected, and go to the Calculations tab on the left-hand side of the TARGIT client:
Now choose Add Top List / Pareto analysis:
Choose ten and top list and click Apply top list changes:
Now your table looks like this - notice it has been sorted descendingly, and only the top 10 are in the table:
Method no 1 is very quick and only has one problem.
You can't add something like a sum of the rest.
That's because this way of making a top list means that TARGIT only will query for exactly these top 10 salespeople's data.
Let's go back to the original table and try method no 2:
- First, sort the table descendingly
- Now go to the calculations tab and add the smart calculation: Rank descending
- Now make a visibilty agent on the Rank with this syntax
- Now your table looks like this - you might finish it up by going to visibility and hide the rank calculation:
- And now you can add a calculated row called the rest with this syntax: sum(0,all(h),m1)
(the "h" means hidden - so only the hidden rows will be summed up):
That was Method no 2.
It was a little more work - but now you have a top list with a sum of the rest, and the extra bonus is, that it can't be broken by sorting data, since the rank calculation, which is the foundation of this, works on both sorted and unsorted data.
Thanks for a great article, Niels! I used Method 2 to come up with a Top 25 customer list with subtotals for top 25 and for “all the rest”.
I ran into one snag if a filter returned fewer than 25 customers. As shown below, the “all the rest” calculation returned “Undefined” if there were fewer than 25 members (i.e. no hidden members), even though I used the 4th parameter.
sum(0, all(h), m1, 0)
My workaround was to create a Top 25 Count calculation, as shown below.
allcount(0, all(v), 0, 0)
Then I modified the “all the rest” calculation to include a condition to check if the top 25 count did not have any hidden members. If there are fewer than 25 members, then it just returns zero.
if sum(0, c1, 0, 0) < 25 then 0 else sum(0, all(h), m1, 0)
Please sign in to leave a comment.