topic Re: Use greater than function in QlikView

Use greater than function

Mon, 11 Aug 2014 15:24:19 GMT

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

Re: Use greater than function

Colin-Albert — Mon, 11 Aug 2014 15:59:33 GMT

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.

Re: Use greater than function

simondachstr — Mon, 11 Aug 2014 16:13:44 GMT

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

Re: Use greater than function

Tue, 12 Aug 2014 09:37:32 GMT

HI,

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

Regards,

Prathamesh Sable

Re: Use greater than function

Tue, 12 Aug 2014 09:41:28 GMT

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

Re: Use greater than function

Tue, 12 Aug 2014 09:41:34 GMT

You can use this in your expression.

sum(if(Column2 > Column1, Column3))

Regards,

Marius

Re: Use greater than function

Tue, 12 Aug 2014 11:48:47 GMT

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

Re: Use greater than function

Tue, 12 Aug 2014 11:52:59 GMT

Hi,

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

Regards,

Marius

Re: Use greater than function

Tue, 12 Aug 2014 12:05:49 GMT

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

Re: Use greater than function

Tue, 12 Aug 2014 12:15:49 GMT

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

That should do the trick.

Regards,
Mariu

Re: Use greater than function

Tue, 12 Aug 2014 14:46:00 GMT

Mariu,

I Tried, Still getting data discrepancy. Using the normal formula

=Sum(IF(Accepted age)>(accept_TM), Total) gives me a count of 23167

performing the same in excel gives me a count of 24175

Same data has been loaded in Qlikview still data error

Regards,

Prathamesh