What is the most efficient way in Looker to use table calc functions to do aggregates based on a separate dimension column?

  • Datameer, Inc.
  • January 24, 2018
Get Started Transforming Your Data in Snowflake

The Problem 

Is there an efficient way to use table calculation functions to do aggregates based on a separate dimension column that could take on arbitrary values (similar to how we might use the AVERAGEIF function in Excel or MIN(IF()) calculation using array formulas)?

Example : 

We have a table in Looker that contains individual orders, and we can calculate the total daily/monthly/yearly/etc. order value from this.

We are now interested in knowing the maximum or minimum daily value of orders by month for the past year or quarter, segmented by different stores.

We could pivot on the store and have the rows correspond to days, then throw a max(if()) function in there for our table calculation, but we would need to specify the “if” condition to a specific month, no? That’d result in a lot of columns: one for each month for each store.

Solution : 

We can use pivoting for partitioning, so we could accomplish what is required  above in one calc with by pivoting store and month doing the following in Looker :

max(

 if(

   diff_months(${my_date}, ${my_pivoted_month}) = 0,

   ${my_measure}, 0)

)

 

This will return a column for every pivot; however, the computation is achieved in one calc. If you’re looking for a way to do this without a column for each pivoted value, that is not available.


Up Next:

Read Where can I find examples of dashboards that others have built-in Looker?