Question

# Ranks with Table Calculations

Userlevel 4
• Looker Staff
• 26 replies

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

• 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:

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!

### 13 replies

Userlevel 1

Hi @ernesto,

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

Userlevel 4

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 🙂

Userlevel 4

That’s a good point @jacopo.chiapparino - 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 🙂

Userlevel 4

Ok done now 🙂

Userlevel 1

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

Userlevel 3

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()))``
Userlevel 1

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

Userlevel 3

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.

Userlevel 3

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

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?

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