Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

removing negative values

Hi Guys,

I need a little help with my expression.

I have the following expression:

Sum(Expenses) / sum(FTE)

This expression displays the expenses per FTE.  There are some company's that have forward their expenses to another company, which results in a negative value.

If I would do a Sum({<Expenses={'>0'}>} Expenses) / sum(FTE), I would get the wrong results, because only the expenses that are above 0 are summed. What I would like to achieve is first do this expression: Sum(Expenses) / sum(FTE), and then use the aggr + if to remove the negative value.

something like this: sum({<Expenses={'>0'}>}(aggr(Sum(Expenses) / sum(FTE), Company))  This expression is totally wrong. But it should display the following:

Companyexpeses per fte
A-2
B-6
C-1
D8
E7
F3

The lowest value should be 3 instead of -6. How can I remove the negative values in my chart?

Another question, Can I sort this table a way that I see the top expeses per fte, right at the top, and the lowest right below it?

Thanks in advance

1 Solution

Accepted Solutions
MayilVahanan

Hi

     If i understand your question, something  like this,

     =if(Sum(Expenses) / sum(FTE) >0, Sum(Expenses) / sum(FTE))

     Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

2 Replies
MayilVahanan

Hi

     If i understand your question, something  like this,

     =if(Sum(Expenses) / sum(FTE) >0, Sum(Expenses) / sum(FTE))

     Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

thanks! that did the job! Sometimes the answer is sooo simple , which makes it hard to guess