topic Re: Need help with a formula in QlikView

Need help with a formula

Thu, 04 Sep 2014 13:30:13 GMT

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

Re: Need help with a formula

MK_QSL — Thu, 04 Sep 2014 13:32:23 GMT

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

Re: Need help with a formula

Thu, 04 Sep 2014 13:37:58 GMT

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

Re: Need help with a formula

Thu, 04 Sep 2014 13:38:04 GMT

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.

Re: Need help with a formula

Thu, 04 Sep 2014 13:38:41 GMT

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

Re: Need help with a formula

sudeepkm — Thu, 04 Sep 2014 13:41:21 GMT

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.

Re: Need help with a formula

MK_QSL — Thu, 04 Sep 2014 13:47:08 GMT

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)

Re: Need help with a formula

Thu, 04 Sep 2014 13:47:09 GMT

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.

Re: Need help with a formula

datagrrl — Thu, 04 Sep 2014 13:53:49 GMT

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.

Re: Need help with a formula

Thu, 04 Sep 2014 14:35:47 GMT

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.

Re: Need help with a formula

MK_QSL — Thu, 04 Sep 2014 14:37:08 GMT

What you will use if SUM([Balance 3]) <= 0?

Re: Need help with a formula

Thu, 04 Sep 2014 14:57:01 GMT

0 then, instead of whatever the negative number would be.