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!)
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))) |
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!
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:
Open up a fresh explore (Explore menu)
Copy the contents of this file to your clipboard
Paste the contents of your clipboard after the url of the explore : https://yourlookerserver/explore/yourmodel/yourexplore/(link from above)
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...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?
@ernesto1 are you able to fix the link to the example report please, it also doesn’t work for me.