Sum Measure with Filter without long SQL?

I have a dimension which is a transaction value

image

I have then created a measure, which does a sum of this transaction value if the transaction is a payment

image

I’m assuming I’ve done something completely wrong here? Since the SQL is really long

And it’s taking quite a while to run.
Should I have done something different for this sum? Where have I gone wrong?

The LookML is correct, and I believe the long SQL comes from Transactions_id being declared as a primary_key. I’m not sure what Looker is doing exactly with the SQL, but I assume it’s making sure it only counts each Transaction_ID’s value once.

Unfortunately I don’t know of any way around it.

Precisely! This is a super useful Looker feature that can seem pretty weird when you first click on that “SQL” tab. @Mintz wrote a great piece about it here:

There actually are some ways to “turn it off”. But, it’s generally doing pretty important work to keep your numbers from being wrong, so you should make sure that your numbers look right both before and after turning it off. And generally, you really shouldn’t turn it off.

Disclaimer out of the way, you can use the symmetric_aggregates parameter https://docs.looker.com/reference/explore-params/symmetric_aggregates to set them not to run on an entire explore. If you’d rather it be on a per-field basis, just specify your measure as a type: number and perform the aggregation in the sql, like

type: number
sql: SUM(${transaction_value}) ;;

Note that this won’t work with the filters parameter you’ve applied, since that doesn’t work on type: number measures.

so tl;dr— You didn’t do anything wrong, this is Looker working hard to keep your data correct. Read that post by mintz, check your join relationships to make sure they’re hunky-dory, and if you absolutely must (or just to satisfy curiousity), you can play with disabling sym-aggs with those two methods above. For more, read Lloyd’s explanation too:

I guess the problem I have with that example is that I’m joining One-To-Many like the example, but I’m summing the Many side, so I don’t see the need for all this extra work.

Saying that though, I’m glad I didn’t do anything wrong, and I’ll check out the symmetric_aggregates, or possibly removing the primary key from the transactions table.

Cheers!