Ranks with Table Calculations

tablecalcs
rank

(ernesto ongaro) #1

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!


(Kyle) #2

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


(ernesto ongaro) #3

That’s awesome @kpmartin87! Thanks for the enthusiasm :blush:

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


(Jacopo Chiapparino) #4

Please do post textual snippets whenever possible :slight_smile:


(ernesto ongaro) #5

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


(ernesto ongaro) #6

Ok done now :slight_smile: