Need help with a formula

[#1]

I have some data in a table, lets say in Name, Country, Balance 1, Balance 2, Balance 3, and Balance 4. Balance 3 = Balance 1 + Balance 2, and I need to take the sum of the numbers of Balance 3 that are > 0, and use it to divide Balance 4. The division part isnt the problem, but trying to aggregate the values in Balance 3 where they are > 0 is where I am having issues. I can't just sum the column, because there are negative numbers there and I cannot use those. Any ideas? Still new to Set Analysis... if that is even what I would use...

[MK_QSL]

Provide some 10 lines data... Difficult to work based on your explanation only...

[#3]

I would advise you to calculate this in the backend and to utilise the Resident Reload / Preceeding Reload functionality

[#4]

Name	Country	Balance 1	Balance 2	Balance 3	Balance 4
Bob	USA	10	10	20	10
Jen	UK	5	10	15	20
James	USA	20	-30	-10	10
Mike	Australia	10	-20	-10	30
Pierre	France	30	10	40	20

So the total I need from Balance 3 is 75, not 55.

[#5]

I actually can't, as I am leaving some stuff out that happens before this, like using input fields.

[sudeepkm]

have you tried.

if(bal1+bal2>0,(bal1+bal2)/bal4) as newcalculatedfield // here those records having negative and 0 will be null.

EDIT: OK just now saw your data. you are looking for aggregated result of bal3 column that you can do it after loading all the data and resident load.

[MK_QSL]

Balance 3 You can use like below

IF([Balance 1] + [Balance 2]<0,0,[Balance 1] + [Balance 2])

Give name to [Balance 3]

Now use below for your requirement

ALT([Balance 4]/[Balance 3],0)

[#8]

I wish that would be easy, but as I mentioned above, alot happens before we get to Column 1 and 2, such as lots of calculations and input fields and so on. So the columns of 1 and 2 are actually calculated fields themselves. But I am using a formula stored in a variable so its actually going to be like the sum of each $(vColumn1) + $(vColumn2) that is greater than 0. I figured I didnt need to go that deep in initially explaining what I was doing as I would just swap out the pieces from the logic someone might provide.

So since i wont know the values there until really the user takes action, I need to do this on the fly.

[datagrrl]

Maybe I don't understand the question, but I think you can just do a SUM(IF(

Maybe this isn't a best practice?I am new to QV as well, so maybe my suggestion isn't the right answer.

SUM([Balance 4])/SUM(IF([Balance 3]>=0,[Balance 3],0))

You need to deal with the Divide by 0 issues (or not), but it sounds like you can handle that.

[#10]

ahhhh ok I didnt know about ALT. But I just want to be sure that I am using the sum of all the records that are > 0 when I divide, not at each individual row. So its like Balance 4 / SUM(Balance 3) but where the sum only includes those numbers that are > 0.