Nested calculations in load script

[#1]

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.',

num(sum(Revenue2010+(Revenue2010*((Employees2011-Employees2010)/Employees2010)))),

  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!!!

[Gysbert_Wassenaar · Accepted solution]

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)

[#3]

I could have sworn I had tried that already!!!!

Thanks for your help 🙂