Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: removing negative values

Hi

     If i understand your question, something  like this,

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

     Hope it helps

2 Replies

Re: removing negative values

Hi

     If i understand your question, something  like this,

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

     Hope it helps

Not applicable

Re: removing negative values

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

Community Browser