Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for
Search instead 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:
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)

Contributor III
Author

Unfortunately that's not an option .. 😞

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

Contributor III
Author

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