Top/Bottom N Filters

Top N Filter is available in Holistics since 26th January 2021.

Top N Filter is a new option in Visualization Settings' Condition that allows you to answer questions like: "What are the top 3 products with the highest revenue?"

In this guide, I will walk you through the steps to use Top/Bottom N Filter.

Context

You have a data model order_items that contain all the transactions of the business.

Table order_items {
order_item_id
order_id
order_created_at
product_created_at
product_name
product_id
quantity
product_price
total_item_value -- item_value = order_item.quantity * product.price
}

From that model, you have built a Product-overview table that contains Product Id, Product Created Date, Product Name, and Product Price. You can see we have many products here.

Now, the question is: We want to filter the table to only display the top 3 products with the highest Revenue (sum of total item value). The expected result looks like below. You can see now we only have 3 products: Body Treatments, Body Cleanser, and Body Scrub, because they have the highest Revenue.

Solution in Holistics

  • Step 1: Explore/edit the current table
  • Step 2: Drag/select the field Product name into Conditions. Select top N filter from the drop-down.
  • Step 3: Since we want to find the top 3 products, we input 3 in N value.
  • Step 4: Since we want to rank the products by their sum of total item value, we select the field Total item value in the By section.

By default, Holistics will select Sum as the aggregate function. You can change that by clicking on Total item value and select from the dropdown list.

Click Apply, then Get Results.

Note: The field Total Item Value does not necessarily need to be used in the table for you to use it in top/bottom N filter.

  • The table is filtered to only show the top 3 products by the sum of total item value. We're seeing 3 different product names here: Body Treatments, Body Cleanser, and Body Scrub.
  • To double-check the result, you might want to create another table that only shows the product name and sum of the total item value, then sort the sum of the total item value column by descending.

  • The top 3 results are: Body Treatments, Body Scrub, and Body Cleanser, which match the 3 product names we see in our original table. Great!

Notes

  1. Holistics does not support more than 1 top N Filter at the same time.

  2. Top N Filter will be applied after all other filters have been applied. For example, let's say we have 2 filters:

    • Product Created Date is between 1st October 2019 and 1st October 2020.
    • Top 3 Product name by sum of total item value

    In this case, Holistics will find all the products that are created between 1st October 2019 and 1st October 2020, then find the top 3 products by the sum of total item value that occurs in that time range.

    As you can see, the 3 products name are now different: Sony Smart TV, Body Cleanser and Sofa 1, which means the date filter is applied before the top N filter.

    If you apply a dashboard filter to a report that already has top N filter applied, the dashboard filter will still be applied first, then the top N filter will be applied to that report.

  3. If there is a tie in the result, then all tied rows are returned. For example, a top 3 filter will not necessarily return only 3 product names, but maybe more names in case 2 products have the same highest sum of total item value.

  4. N value must be a positive integer.