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!

How it works

Use dense rank to return the top N records

Top/bottom N items, based on a value, return the list of items with the values in the top N. Behind the scenes, Holistics use dense rank to return the top N records from each group with no gaps in the sequential rank numbering of rows in each windowing partition. Therefore, there might be more than N items because some might share the same highest values.

For example, Top 10 products by Gross Number Of Orders Items sold return 12 products.
This because products that have the same Gross Number Of Orders Items sold will have the same rank. Top 10 filter will show items from #1 to #10, the 12th item is #9, so it also appears in the report.

Top N Filter will be applied after all other filters have been applied.

Before you begin filtering Top/Bottom N value, it's important to understand the order in which Holistics executes filters in your report.

Top N Filter is one type of Widget Filters, and will be applied after below filters:

• The dashboard filter
• All other widget filters

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.

Notes

1. Holistics does not support more than 1 top N Filter at the same time.
2. N value must be a positive integer.