1. TARGIT Community
  2. TARGIT Tips and Tricks
  3. Tips and Tricks - 2022 Christmas edition

December 15th: The unbreakable toplist with a sum of the rest

Avatar
Niels Thomsen

When you make a top list in TARGIT, you have at least two options: 

  1. Using the built-in top list function
  2. Creating your top list with a visibility agent

Here's a table to illustrate, showing revenue by salesperson:

mceclip0.png

 

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:mceclip1.png

 

Now choose Add Top List / Pareto analysis:

mceclip2.png

 

Choose ten and top list and click Apply top list changes:

mceclip3.png

 

Now your table looks like this - notice it has been sorted descendingly, and only the top 10 are in the table:

mceclip4.png

 

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:

  1. First, sort the table descendingly
    mceclip5.png

  2. Now go to the calculations tab and add the smart calculation: Rank descending
    mceclip6.png
  3. Now make a visibilty agent on the Rank with this syntax 
    mceclip7.png

  4. Now your table looks like this - you might finish it up by going to visibility and hide the rank calculation:
    mceclip9.png

  5. 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):
    mceclip10.png

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.

 

 

 

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

Comments

1 comment
Date Votes
  • Avatar
    Mark Frye

    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)

    0

Please sign in to leave a comment.

Articles in this section

See more