Row-wise MoM Calculations

This one came up with a client recently, where a user wanted to calculate month-over-month changes for a number of different categories, in a result table with one row per category + month. It’s quite a nice example as it involves a simple but inflexible solution, and a more robust version which demonstrates another useful Looker expression function.

Starting with a simple result table of two months’ sales data for a selection of product categories, you can do a very quick month-over-month calculation by using the offset function and specifying the right number of rows (6 in this case) to look down the table to get the previous month’s value for each category to use in the calculation.

This is fine as a quick solution, but relies on the table being sorted by month and then category. Incidentally, you can do that by sorting by month first, then holding shift when you click the header for category, to get the table sorted by month and then category - the headers will show a ‘1’ and ‘2’ to show how it’s sorted.

A further problem with this basic calculation is that it’ll break if any categories don’t appear in the table for both months. By using some extra calculation magic we can deal with this, though!

First, we need to add the ‘month num’ timeframe to the table. Then we can add a calculation to combine the month number and category using the concat function. Then finally we can construct our robust MoM calculation. This works by:

• subtracting 1 from the month number to get the number of the previous month
• using concat to combine this with the category name
• using that value and the lookup function to get the sales for that category for the previous month
• calculating the MoM % by using this value and the current month’s sales

It might be a bit complicated but this is a robust way to set up this calculation - just to prove it I sorted by category and then month ascending, and we still have the correct values! Now you can hide the columns you’re not interested in and add this to a dashboard :)

You could probably do this without needing the month number column and by using even more functions to extract the month number from the month column in order to subtract one from it, but the calculation is complicated enough already!