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

[klrameet]

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

[jwjackso]

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

Data:
Load * Inline [
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)
Load EMPLID,
Sum(BAL_CURR_MONTH) as BAL_CURR_MONTH_TOTAL
Resident Data
group by EMPLID;

Your formula becomes

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

[klrameet]

Unfortunately that's not an option .. 😞

[Kushal_Chawda]

try below

Data:
Load * Inline [
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)

[klrameet]

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