I have a use case to get “SUM DISTINCT” in an aggregate model vs just SUM. SUM is causing some values to be double counted and inflating the value.
I know COUNT DISTINCT is supported, but wondering if there is a clever way to achieve “SUM DISTINCT” or if that is a feature that should be added to Skuid.
I’d love to get some input from the community.
Thanks in advance,
Andrew
Let’s think of a scenario in which I have a number field called Total Apples. If Record 1 has a 7 in this field, and Record 2 does too, your need is to have that 7 included only once in the sum, instead of twice?
If that’s what you’re after, there doesn’t seem to be a straightforward declarative way to accomplish this, though that doesn’t preclude a scripted solution. Since we always encourage declarative solutions, my inclination would be to try and set up a condition on your aggregate model that only pulls in one record per each value in Total Apples. That may require subqueries and other fancy footwork though, which gets a bit in the weeds.
Thanks for the reply Mark.
It might be slightly different in my case. I have an aggregate model with tasks and I want to SUM a Lifetime Value field (currency) on Account. I’m going from Task -> Contact -> Account. The challenge I’m having is that if I have two Tasks on two separate Contacts that are from the same Account then Lifetime Value is getting double counted. I only want it to be counted once in this case.
COUNT DISTINCT handles this properly, so I’d basically want the exact same functionality but with SUM.
I hope that makes sense.
You probably just want to create a model on accounts, with conditions that limit the accounts to the ones in the relevant tasks and include the lifetime value field. Then you should be able to use some kind of model lookup formula field on your tasks agg model to grab the right value from the accounts model. (i think?)
