topic Set Expression: Filter by conditional aggregate in App Development

Set Expression: Filter by conditional aggregate

Kellerassel — Thu, 14 Dec 2023 13:19:28 GMT

I have data in this shape:

What I need is the distinct numbers of Ids where gross revenue is >20 as a chart formula.

Basically this:

Count(Distinct {<[Id]={"=Sum({<metric={gross}>} revenue)>=20"} >} Id)

which doesn't work of course.

Any help would be greatly appreciated.

Kellerassel — Fri, 15 Dec 2023 07:37:16 GMT

This does the job using an If()-statement instead of a set expression.

Count(Distinct If(Aggr(Sum({<metric={'gross'}>} revenue), Id) > 20, Id))

But maybe there is a more performant solution inside set expression

vincent_ardiet_ — Fri, 15 Dec 2023 08:54:56 GMT

The first expression should work and be more performant, what was the issue with it?

rajjul — Fri, 15 Dec 2023 09:06:44 GMT

Not sure why you are using SUM here ...?

try this whether it works

Count(Distinct{<[revenue]={">20"}>}Id)

Kellerassel — Fri, 15 Dec 2023 09:21:01 GMT

Now I am utterly confused.. Yes, it does work. Now! I have no clue why it didn't work yesterday.