Sum of distinct value with a condition

[jorgen_santberg]

Hi all,

I have a question regarding the following table:

Customer	Value	Days
A	100	10
A	-100	1
B	150	15
C	50	10
D	80	11
D	-60	5
E	50	5
F	200	8

The result I am trying to get in a text box is the sum of days of the customers. If the sum of value for a customer is 0 than the days are not relevant. If there are 2 or more rows for a customer (and the sum of value <>0) than only the max Days for that customer is relevant.

For example:

- Customer A should be neglected because the sum of value is 0

- The value Days for Customer D should  be 11.

The sum of days I want as a result is 15+10+11+5+8=49 (marked green in the table above).

I hope you guys can help me out!

[neelamsaroha157]

Can you do these condition checks in script and put a flag, then use these flags in front end or do you have to do it in front end only?

[Or · Accepted solution]

File that one firmly in the multi-aggr() pile of "yuck" formulas, but:

sum(Aggr(if(aggr(Sum(Value),Customer)<>0,Max(Days),0),Customer))

[neelamsaroha157]

Something like this

[jorgen_santberg]

I will file it there. For now, it works just the way I want, thanks!

[jonathandienst]

How about:

Aggr(If(Sum(Value) > 0, Max({<Value = {">0"}>}Days)), Customer)