Grouping by a dimension in table calculations


(Rob Schoenbeck) #1

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?


(Zachary Michel) #2

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.


(Elliott Collins) #3

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?


(Rob Schoenbeck) #4

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.