Aggr and then count distincts

[Anonymous]

Hi

I want to calculate a KPI to show the number of orders with total quantity no more than 100 units. However my data is in order line level and needs to be aggregated first. I am assuming a combination of aggr and count distinct but can't really structure the formula.

Order	Order line	Units
A	1	50
A	2	40
A	3	20
B	1	20
B	2	10
C	1	8
C	2	40
C	3	20
C	4	3

Thanks in advance

[sunny_talwar · Accepted solution]

May be like this

Sum(Aggr(If(Sum(Units) < 100, 1, 0), Order))

The above should give you 2 (B, C)

[Anonymous]

Thanks! Can't believe I spent more than an hour for such a simple one!

[swuehl]

Or maybe using set analysis with an advanced search like

=Count({<Order = {"=Sum(Units)<=100"}>} DISTINCT Order)