Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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)
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)
thanks but i dont have option to load data this way as i need the breakdown for someother purpose.