Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
jorgen_santberg
Contributor III
Contributor III

Sum of distinct value with a condition

Hi all,

I have a question regarding the following table:

   

CustomerValueDays
A10010
A-1001
B15015
C5010
D8011
D-605
E505
F2008

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

View solution in original post

5 Replies
neelamsaroha157
Specialist II
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?

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

neelamsaroha157
Specialist II
Specialist II

Something like this

jorgen_santberg
Contributor III
Contributor III
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

How about:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein