Question

Grouping by a dimension in table calculations

  • 5 February 2016
  • 3 replies
  • 3304 views

I’m wondering if there’s a clean way to use table calculation functions to do aggregates based on a separate dimension column that could take on arbitrary values (similar to how you might use the AVERAGEIF function in Excel, or MIN(IF()) calculation using array formulas).


Example:

You have a table that contains individual orders, and can calculate the total daily/monthly/yearly/etc. order value from this. Let’s say you are interested in what is the maximum or minimum daily value of orders by month for the past year or quarter, segmented by different stores.


You could pivot on the store and have the rows correspond to days, then throw a max(if()) function in there for your table calculation, but you’d 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.


Is there a more efficient way to go about this?


3 replies

Userlevel 4
Badge +1

Hey @rschoenbeck,


Currently we use pivoting for partitioning, so you could accomplish what you wants in one calc with by pivoting store and month doing the following:


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, then that is not available, however, that is something we are looking to achieve in the near future.

I’m trying to get the sum of a variable within each group. So just to double check, is there still no GROUP BY equivalent in Looker? Or do I just need to switch to SQL?

That’s essentially the definition of what dimensions in Looker do: they select columns which go into a GROUP BY statement, and then measures are aggregate functions that within the groups that dimensions define.

Reply