LinkedIn
Twitter
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))) |
-
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!
