Use greater than function

[#1]

Hi All,

I need to calculate the sum of a column based on greater than functionality of other 2 columns but I am not able to do the same.

E.g.

Column1 Column 2  Column 3

01              02            10

02              03             10

01              02             10

What I am trying to achieve here is

If(Column2) > Column (1) then Sum( Total)

Which means it should provide me a total sum of all Column 3 where Column2 > column 1

I tried deriving an expression; but did not work.

Any help on this would be highly appreciated as I am stuck on this for Quite a few hours.

Regards,

Prathamesh Sable

[Colin-Albert]

Create a new column in your load script, then sum that column.

The formula in your load script would be

     if(Column2 > Column1, Column3) as Total

Then just use   Sum(Total)      in your chart.

[simondachstr]

Try also maybe using RangeSum() to make sure NULL's are treated as 0's.

[#4]

HI,

Thanks for the reply. Is this possible to derive this in a expression?

Regards,

Prathamesh Sable

[#5]

Hi Martin,

Tried range Sum(). Did not work.

Here is the expression what I am trying to derive

=If(Only( Accepted_age) >= only (Accept_TM), RangeSum(Total))

The output that I want to extract is if accepted age is greater than equal to accept team then I should get the values of total field where the accepted age values are greater than accept team values.

Please assist.

Regards,

Prathamesh Sable

[#6]

You can use this in your expression.

sum(if(Column2 > Column1, Column3))

Regards,

Marius

[#7]

Hi Marius,

Now my Column 1 & 2 has blanks & some values which needs to be excluded in the the above calculation.

Is there a way I can achieve the same?

For e.g. sum(if(Column2 (exclude blanks) > Column1 (exclude value), Column3))

something of the sort in set expression

=SUM({$<[Accepted Date]={'-'},lead_Reason={'Expired'}> }Total)

How to achieve the same?

Regards,

Prathamesh Sable

[#8]

Hi,

sum(if(Column2 <> '' and Column1 <> '' and Column2 > Column1, Column3))

Regards,

Marius

[#9]

Hi Marius,

Thanks for your prompt reply. The values are not being pulled up correctly.

In Excel in am trying to use the formula as -

=IF(ISBLANK([@[Accepted_age]]),IF([@[lead_Reason]] = "Expired","Expired","Not Accepted"),IF([@[Accepted_age]]>[@[Accept_TM]],"No","Yes"))

in Qlikview I want o use the same sort of expression where-in I need filter the columns [Accepted_age] is blank lead reason is expired & [Accepted_age]>[Accept_TM]

Thanks for your assistance.

Regards,

Prathamesh Sable

[#10]

=sum(if([Accepted_age] = '' and lead_Reason = 'Expired' and [Accepted_age]>[Accept_TM], Column3)).

That should do the trick.

Regards,
Mariu