Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mazman65
Not applicable

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
Not applicable

Re: greater than and less than

try  this.

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

or

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

12 Replies
mazman65
Not applicable

Re: greater than and less than

DIFFERENCE is the name of the Column

beck_bakytbek
Not applicable

Re: greater than and less than

try to use conditional in folder Layout

Beck

beck_bakytbek
Not applicable

Re: greater than and less than

or try this


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

Beck

mov
Not applicable

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

Highlighted
MarcoWedel
Not applicable

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
Not applicable

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

MarcoWedel
Not applicable

Re: greater than and less than

Sum(Fabs(DIFFERENCE))

maverjannet
Not applicable

Re: greater than and less than

fabs(Sum(DIFFERENCE))



mazman65
Not applicable

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