Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Capturing "top" entities/values, using a dimension

  • 16 December 2016
  • 1 reply
  • 728 views
anika
Rank
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

M
Rank

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


Terms & Conditions