Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Sum of distinct value with a condition

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!

1 Solution

Accepted Solutions
MVP

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))

5 Replies
Specialist II

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?

MVP

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))

Specialist II

Something like this

Contributor III
Author

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

Partner - Champion III