# 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

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.

## Related Posts

### How to add custom visualization in Looker using javascript?

• Looker FAQs
• January 15, 2018

### How to change the order of stacks in a stacked area chart...

• Datameer, Inc.
• January 25, 2018

### How to comment on an entire block of code in the LookML e...

• Datameer, Inc.
• January 22, 2018