Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm trying to use a series of if statements in the load script. I've done successfully in the past but not with nested calculations so I hope somebody can help.
Here is what I'm working on:
if(len(trim(Revenue2011))=0 or Revenue2011='n.a.' and len(trim(Employees2011))>0 and Employees2011 <> 'n.a.'
and len(trim(Revenue2010))>0 and Revenue2010 <> 'n.a.' and len(trim(Employees2010))>0 and Employees2010 <> 'n.a.',
Revenue2011) as Revenue2011
I've highlighted the calculation the I can't get to work. Any ideas? Below is a table with some data to test it on and the actual results I'm trying to achieve with this calculation.
Company Name | Revenue2011 | Employees2011 | Revenue2010 | Employees2010 | Results |
Company A | n.a. | 95 | $65.150.000,00 | 87 | $71.140.804,60 |
Company B | n.a. | 20 | $2.190.000,00 | 18 | $2.433.333,33 |
Company C | 15 | $8.957.000,00 | 11 | $12.214.090,91 | |
Company D | n.a. | 3.689 | $1.516.392.000,00 | 3.986 | $1.403.404.437,53 |
Company E | n.a. | 65 | $6.551.000,00 | 65 | $6.551.000,00 |
Company E | n.a. | 394 | $1.058.334.000,00 | 200 | $2.084.917.980,00 |
Company F | $3.500.000,00 | 23 | $3.253.000,00 | 21 | $3.500.000,00 |
In reality, the scrip would be much more complex with several if statements like this but as long as I understand how to make the calculations work I should be fine.
Thanks!!!
You're using sum in your expression without using a group by clause. If you remove that sum then the expression should work.
Maybe this would do what you need too:
round(if(IsNum(Revenue2011),Revenue2011,Revenue2010*Employees2011/Employees2010),1/100)
You're using sum in your expression without using a group by clause. If you remove that sum then the expression should work.
Maybe this would do what you need too:
round(if(IsNum(Revenue2011),Revenue2011,Revenue2010*Employees2011/Employees2010),1/100)
I could have sworn I had tried that already!!!!
Thanks for your help 🙂