Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

mazman65
New Contributor III

greater than and less than

I have a column of $ values- both negative(-$14.27) ,Positive($14.27) and Balanced( $0.00).

I want to do a Text Object to display the SUM of the Greater Than $0.00 and the Less Than $0.00.

I am using this expression

=sum(DIFFERENCE <'0') & (DIFFERENCE>'0')

But it is only returning the SUM of the negative(-$14.27).  If I switch the order of the Greater and Less, it then will SUM only the positive.

What would be the correct expression?

Thanks!

Dan

1 Solution

Accepted Solutions
maverjannet
Valued Contributor

Re: greater than and less than

try  this.

if(DIFFERENCE<>0,count(DIFFERENCE))

or

if(DIFFERENCE<>,fabs(Sum(DIFFERENCE)))

12 Replies
mazman65
New Contributor III

Re: greater than and less than

DIFFERENCE is the name of the Column

beck110979
Valued Contributor III

Re: greater than and less than

try to use conditional in folder Layout

Beck

Highlighted
beck110979
Valued Contributor III

Re: greater than and less than

or try this


=If(sum(DIFFERENCE <0, '-14,25') and if (sum(Difference>0, '14,25')

Beck

mov
Esteemed Contributor III

Re: greater than and less than

If it worked, you'd have no space between two numbers, that is if the first result is -15 and the second (if worked) is 1o, you'll get

-1510

Try this:

='Negative:  ' & sum(if(DIFFERENCE<0,DIFFERENCE)) & chr(10) & 'Positive:  ' & sum(if(DIFFERENCE>0,DIFFERENCE))

Re: greater than and less than

Hi,


another solution could be:


=Sum(RangeMax(DIFFERENCE,0))&'  '&Sum(RangeMin(DIFFERENCE,0))

hope this helps

regards

Marco

mazman65
New Contributor III

Re: greater than and less than

Michael,

The result of yours is close,

Negative: 4
Positive: 12

But I would like one Total Number of Negative & Positive.

Total: 16

Re: greater than and less than

Sum(Fabs(DIFFERENCE))

maverjannet
Valued Contributor

Re: greater than and less than

fabs(Sum(DIFFERENCE))



mazman65
New Contributor III

Re: greater than and less than

Wedel & Janet,

when I use SUM, it Totals all the $ amounts in the table, returning a large Dollar Amount($233590.64)

If I change it to COUNT, it also counts ALL the rows in the table(88).

I want it to total just the rows that are >$0.00 and <$0.00.  I do not want to count the $0.00.

This example of the data in the table, I want for to return

Total: 5

 

DIFFERENCE(Field Name)
$0.00
$45,724.85
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$0.00
$47.76
$0.00
$0.00
$0.00
$0.00
$5,044.00
$0.00
$0.00
$0.00
$0.00
-$7,581.05
$0.00
$0.00
$0.00
$45,724.85