Solved: Re: greater than and less than - Qlik Community

Anonymous · ‎2015-04-30

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

Anonymous · ‎2015-04-30

try this.

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

or

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

Anonymous · ‎2015-04-30

DIFFERENCE is the name of the Column

beck_bakytbek · ‎2015-04-30

try to use conditional in folder Layout

Beck

beck_bakytbek · ‎2015-04-30

or try this

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

Beck

Anonymous · ‎2015-04-30

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

MarcoWedel · ‎2015-04-30

Hi,

another solution could be:

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

hope this helps

regards

Marco

Anonymous · ‎2015-04-30

Michael,

The result of yours is close,

Negative: 4
Positive: 12

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

Total: 16

MarcoWedel · ‎2015-04-30

Sum(Fabs(DIFFERENCE))

Anonymous · ‎2015-04-30

fabs(Sum(DIFFERENCE))

Anonymous · ‎2015-04-30

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