Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
klrameet
Contributor III
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)
4 Replies
jwjackso
Specialist III
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)

klrameet
Contributor III
Contributor III
Author

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)

Annotation 2020-08-30 233139.png

klrameet
Contributor III
Contributor III
Author

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