Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Company | expeses per fte |
---|---|
A | -2 |
B | -6 |
C | -1 |
D | 8 |
E | 7 |
F | 3 |
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
Hi
If i understand your question, something like this,
=if(Sum(Expenses) / sum(FTE) >0, Sum(Expenses) / sum(FTE))
Hope it helps
Hi
If i understand your question, something like this,
=if(Sum(Expenses) / sum(FTE) >0, Sum(Expenses) / sum(FTE))
Hope it helps
thanks! that did the job! Sometimes the answer is sooo simple , which makes it hard to guess