Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like this
Sum(Aggr(If(Sum(Units) < 100, 1, 0), Order))
The above should give you 2 (B, C)
May be like this
Sum(Aggr(If(Sum(Units) < 100, 1, 0), Order))
The above should give you 2 (B, C)
Thanks! Can't believe I spent more than an hour for such a simple one!
Or maybe using set analysis with an advanced search like
=Count({<Order = {"=Sum(Units)<=100"}>} DISTINCT Order)