Calculate Sum of selected values in a column?

[#1]

Hi All,

First of all I tried searching for this topic did not find it...if there is already one please redirect me to that link.

Problem : I have two columns called sales_increment and profit_increment. I have to calculate the sum of all sales_increment when sales_increment > 0 and profit_increment>0.

I am trying to display the result in a text box.

Expression used :

Sum(If(INCREMENTAL_SALES>'0' and INCREMENTAL_PROFIT>'0',INCREMENTAL_SALES))

I am not getting the right answer. Can anyone please look into this??

[Sokkorn]

Hi,

Let try this expression in text box (must use = sign)

=SUM({<INCREMENTAL_SALES={">0"}, INCREMENTAL_PROFIT={">0"}>} INCREMENTAL_SALES)

Do let me know after you tested.

Regards,

Sokkorn

[#3]

Please try the following:-

Sum(If(INCREMENTAL_SALES>0,if(INCREMENTAL_PROFIT>0,INCREMENTAL_SALES)))

Regards,

Vineet

[#4]

Please try the following:-

sum(if(INCREMENTAL_SALES>0,IF(INCREMENTAL_PROFIT>0,INCREMENTAL_SALES)))

Regards,

Vineet

[#5]

Hey Sokkorn,

Thanks for the reply.

I got the same result from your expression as I did for my expression. There seems to be a problem in the data set I am using. I will try changing the data set. Give me some time and I will check that with the new data set and reply.

[#6]

Hi Vineet,

Thanks for the reply.

Again, I have got the same result with your expression. I am in the process of changing the data set ..will create a test data set and let u ppl know .

[#7]

Sokkorn,

Can you please explain me take me through your expression : =SUM({<INCREMENTAL_SALES={">0"}, INCREMENTAL_PROFIT={">0"}>} INCREMENTAL_SALES)

I am new to this and facing a little problem in understanding the syntax. I had used a similar expression(with the same result agaiin : =SUM({<INCREMENTAL_SALES={">0"}* INCREMENTAL_PROFIT={">0"}>} INCREMENTAL_SALES)) but with little understanding of the syntax. can you please take me throuh the syntax?

[Sokkorn]

Hi udayshankark,

It may better if you can share your app with desire result. We look into it together to fine solution.

Regards,

Sokkorn

[Sokkorn]

Hi udayshankark,

This is we called Set Analysis. My expression will sum INCREMENTAL_SALES where INCREMENTAL_SALES and INCREMENTAL_PROFIT bigger than 0. It seem like your expression too, but this one will performe more better than IF statement.

Attached doc will explain more details.

Regards,

Sokkorn

[#10]

Here it is