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).
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?