Capturing "top" entities/values, using a dimension

top10
top
pivot

(Anika Kuesters Smith) #1

It’s often desirable to capture only the top few of something–for example, to compare web traffic to our top ten countries over time, or sales of our highest-margin brands.

This can be accomplished by ranking in a derived table (view an example of doing this here), but this can also be accomplished in a simple dimension:

  - dimension: is_top_ten_countries_by_quantity
    type: yesno
    sql: |
      ${country} IN
      (SELECT country FROM
        (
          SELECT
            country
          FROM public.order_items AS order_items
          WHERE {% condition date_paid_date %} order_items.date_paid {% endcondition %}
          GROUP BY 1
          ORDER BY SUM(quantity) DESC
          LIMIT 10
        ) sub )

This example can have a templated filter for date_paid passed in; this can be removed, or other filters (for example on category or department) could easily be added.


Create an "Other" bucket in a chart
(Max Reid) #2

Awesome pattern, Anika.

If you want the ranking to dynamically recalculate based on another date filter (say, the created date of an order), here’s a slightly modified version. Now, whatever filter is being applied to created date will also apply to this ranking.

dimension: is_top_ten_countries_by_quantity {
type: yesno
sql: |
  ${country} IN
  (SELECT country FROM
    (
      SELECT
        country
      FROM public.order_items AS order_items
      WHERE {% condition created_date %} order_items.date_paid {% endcondition %}
      GROUP BY 1
      ORDER BY SUM(quantity) DESC
      LIMIT 10
    ) sub ) ;; }