2 Replies Latest reply: Aug 13, 2012 1:09 PM by Carter James RSS

    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