Using Merged Results to Do Period Comparisons

lookml

(robert.grace) #1

Comparing results between time periods can often seem daunting at first glance. Many people fall down the rabbit hole of stacking many calculations into one table using offsets. While this can be fast and efficient for very sophisticated developers, it can also make coding and visualizing tedious. Furthermore, since these calculations are created in the explore layer, the more complex the calculations become the more crucial it is for power users to coordinate their development to maintain a single source of truth.

Fortunately, with Merged Results and some creative self-joins, Looker can do these types of comparisons for you in a quick and governed fashion!

The crux of this approach revolves around three instances of the same query joined to itself using the power of merged results.

For our primary query, we want to create a result set with 4 total fields: the order created week, the week directly prior to that period, the week 52 weeks prior to that period, and the order count of the order created week. Essentially, our goal here is to create a count of orders by week and then two join key columns so that Looker can help us match the periods in our merged results.

For Created Week and Order Count, we can simply select the dimension and measure from our field picker in the Orders table. To created our Week-over-Week and Year-over-Year join keys, we do two simple table calculations.

Week-over-Week:

add_days(
-7,
${orders.created_week}
)

Year-over-Year:

add_days(
-364,
${orders.created_week}
)

Our final result of the first query should look like this:

We might also consider adding a filter to limit the size of our data set. As the primary query contains the join keys, limiting this query will automatically limit the next two queries as well in the final results set. In our example I’ve limited the primary data set to 10 weeks.

Now we need to create the next two queries to join in our merged results, but there’s good news: all the heavy lifting is already done!

For the next two queries we simply select Created Week and Order Count. The output of these queries will look like this:

We differentiate between Weekly and Yearly periods based on our join, so creating the queries themselves was a snap!

Now we are ready to put it all together. Let’s start with our original query with the join keys, and then add two versions of the Created Week and Order Count query in our merged results:

After that, we go to our merge rules section to set up our joins. For the Week-over-Week Join from the first query let’s merge it with Created Week from the second query. Similarly, we can merge the YoY Join from the first query to the Created Week from the third query.

Our results set should now consist of 6 columns: the Created Week, the Previous Created Week, and the Week from the Previous Year which correlates to the Created Week. We will also have the order count from those three periods in order.

Now we can create some basic table calculations to calculate Week-Over-Week Change and Year-over-Year Change. Since the heavy lifting of syncing up our periods was already done in the merged results, we can simply take the current week orders divided by the comparison period orders and subtract one since we are formatting as a percentage.

Week-over-Week % Change:

${orders.count}
/
${q1_orders.count}

  • 1

Year-over-Year % Change:

${orders.count}
/
${q2_orders.count}

  • 1

This opens up a host of visualization options to neatly display comparisons from previous periods on one chart using only one time dimension!

One thing you’ll want to decide as you’re building out your visualizations is which columns to show and hide. You will always want to hide the YoY and WoW Join columns, and depending on the situation you may want to hide order counts from some or all of the periods.

For instance, on a line chart, you might want to hide everything except the created week and % change calucluations:

On other charts, such as a heatmap, may want to add in the actual order count from the period we are comparing.

We should note that you aren’t confined to comparing by week with this whole approach. You can use quarters, years, or whatever custom time period you decide as long as you set up your join keys in your primary query correctly.

One final thing to keep in mind – the merged result query does not interact with filters yet, but that is coming in a future release. In the mean time, if you have a static set of filter criteria you can apply them when building the initial queries and those results will still be applied when merging your queries together.


(Luc) #2

Am I correct in assuming this only works if you base your WoW/YoY join columns on a date_add logic as opposed to a date_month logic? I am trying to do it that way (since months don’t have a fixed length), but the results for my calculated column are null.

UPDATE: I fixed my issue by adding another Custom Month Dimension to link to. The base dimension and the merge dimension need to have the same format which doesn’t happen naturally with month (format 2019-01) and add_months (format 2019-01-01).