Ranks with Table Calculations

It’s tempting to create a “top 10” style report and call it quits, often times it is more useful to understand the relative ranking of movement based on a time period; like how popular was this item last month vs last year?

The end result is something like this, high information density in an easy to read format. You can see that adidas has been growing steadily for a year or that Levi’s has not moved from it’s top spot (at the time of this post!)

Explore Data in Full Screen

There are a few table calculations, filters and pivots happening in the background.

Action Description Code
rank (table calc) this gives us the current position of the item rank(${order_items.count},${order_items.count})
date filter this gives us the last 2 complete months and then this month last year 12 months ago for 1 month,2 months ago for 2 months
pivot on month this gives us a pivot by time period make sure to sort them so that your pivot_index works and that dates are not filled in
rank(this month) this lets us extract the rank from each time period pivot_index(${rank},1)
rank(last month) hidden pivot_index(${rank},2)
rank (last year) hidden pivot_index(${rank},3)
month over month change hidden ${rank_last_month} - ${rank_this_month}
month over year change hidden ${rank_last_year} - ${rank_this_month}
movement (month) show position last month if(${month_over_month_change} > 0,concat(" ▲ ", ${month_over_month_change}),,if(${month_over_month_change} < 0, concat(" ▼ ", ${month_over_month_change}),,if(${month_over_month_change} = 0, "-",,null)))
movement (year) show position last year if(${month_over_year_change} > 0,concat(" ▲ ", ${month_over_year_change}),,if(${month_over_year_change} < 0, concat(" ▼ ", ${month_over_year_change}),,if(${month_over_year_change} = 0, "-",,null)))

  • Tip on hidden measures: keep the visualization clean by hiding the intermediate calculations
    c5ecb5845e39cf8b0c5bd328b955787f3835289b.png
  • Tip on filled in dates: since we have three months with 12 months blank in between them, Looker may try and put in the missing months, disable this:
    20e5520defc9a635a056f43d1e492719df7ddad5.png

Of course you can also do a lot of this in LookML and get pretty fancy with the html output - table calculations is only one of the ways to do rankings in Looker!

11 13 21K
13 REPLIES 13

kpmartin87
Participant III

Hi @ernesto1,

This is great and a much cleaner approach than what I have done in the past!!! I used a lookup function rather than pivoting months. This caused with my rank function to not work correctly if a brand did not show up in a previous month. To correct this I wrote a window function in LookML. I will definitely have to try this out to see how it works for my use case!

Best,
Kyle

That’s awesome @kpmartin87! Thanks for the enthusiasm 😊

I’ve added live examples to the post if you want to cut/paste into your instance

Please do post textual snippets whenever possible 🙂

That’s a good point @jacopo_chiappar - I’ll try and re-write the article so it’s easier to copy and paste.

Check out this trick:

  1. Open up a fresh explore (Explore menu)

  2. Copy the contents of this file to your clipboard

  3. Paste the contents of your clipboard after the url of the explore : https://yourlookerserver/explore/yourmodel/yourexplore/(link from above)

  4. You’ll find all the calculations from the example in the calculations section 🙂

Ok done now 🙂

I’ve had a few people ask how to do a reverse rank and I’ve found the best way to do so is by using something like : 

max(row())-rank(${order_items.count}, ${order_items.count})+1

where the max() is going to return a reverse rank starting with 0 which is why we add the +1 so that it start at 1 instead 

I’ve also seen some people ask for a dense_rank, which can be done like this:

count_distinct(offset_list(${field_to_rank}, -1 * row() + 1, row()))

@chris.seymour I tried this dense-rank function, but for me it seems like just ordering the rows, nothing else.

A dense_rank function works just like a rank function, but it assigns consecutive ranks instead of leaving gaps between the values.  So if your data was [100, 90, 90, 80], the rank would return [1,2,2,4] while the dense_rank would return [1,2,2,3].

If this is not what you would like to see, could you elaborate on what you’d like this function to accomplish?

What if we want to partition our data so as to get the top 10 customers from each city. Rather than just ranking all the people.

Hey @sanyam, there are some tips on doing window functions with table calculations in this post: 

https://community.looker.com/explores-36/creating-a-window-function-inside-a-table-calculation-custo...

kccanut
Participant I

Hello, am I the only one who cannot see the visualization and cannot access the data in the link?

(Im logged in)
If so, can you help me with that?

39ebf2e2-06b8-4fff-994d-913ba8ebba39.png

@ernesto1 are you able to fix the link to the example report please, it also doesn’t work for me.

c905c89f-107d-448c-9966-017b0ffdcc63.png
Top Labels in this Space
Top Solution Authors