Announcements
cancel
Showing results for
Did you mean:
Contributor III

## sum the balances, only if the sum is greater than 0 for a emoployee id

have the data for balances by employees where there are multiple rows per employee with few balances in positive and few in negative.

Emplid BalCurrMonth

123              -100

123              -100

123               300

456              -100

456              -100

456               100

456                 50

565                 50

565                 50

i am trying to sum only those, if the total for an employee is positive, where as the below expression is summing the positive balances only, regardless whether the total for employee is positive or negative.

Total i am expecting here is 200

123:(-100-100+300) =100

565:(50+50) = 100 & i am probably getting 400.

what am i doing wrong here , i tried two but none seem to give the right answer:

Sum({< BAL_CURR_MONTH = {"Aggr(Sum(BAL_CURR_MONTH),EMPLID) > 0"} >} BAL_CURR_MONTH)

SUM(If(Aggr(Sum(BAL_CURR_MONTH), EMPLID)>0,BAL_CURR_MONTH,0))

Labels (3)

• ### Sum Total

4 Replies
Specialist III

Not exactly what you are asking, but sometimes it is easier to flag items in the load script.  Using the data below:

Data:
EMPLID,BAL_CURR_MONTH
123,-100
123,-100
123,300
456,-100
456,-100
456,100
456,50
565,50
565,50
];
Left Join (Data)
Sum(BAL_CURR_MONTH) as BAL_CURR_MONTH_TOTAL
Resident Data
group by EMPLID;

=Sum({<BAL_CURR_MONTH_TOTAL={">=0"}>}BAL_CURR_MONTH)

Contributor III
Author

Unfortunately that's not an option .. 😞

try below

Data:
EMPLID,BAL_CURR_MONTH
123,-100
123,-100
123,300
456,-100
456,-100
456,100
456,50
565,50
565,50
];

Expression will be below

=sum({<EMPLID={"=sum(BAL_CURR_MONTH)>0"}>}BAL_CURR_MONTH)

Contributor III
Author

thanks but i dont have option to load data this way as i need the breakdown for someother purpose.