Capturing "top" entities/values, using a dimension

  • 16 December 2016
  • 1 reply
  • 728 views

Userlevel 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.


1 reply

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 ) ;; }

Reply