Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Nested calculations in load script

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 NameRevenue2011Employees2011 Revenue2010Employees2010 Results
Company An.a.95 $65.150.000,0087 $71.140.804,60
Company Bn.a.20 $2.190.000,0018 $2.433.333,33
Company C
15 $8.957.000,0011 $12.214.090,91
Company Dn.a.3.689 $1.516.392.000,003.986 $1.403.404.437,53
Company En.a.65 $6.551.000,0065 $6.551.000,00
Company En.a.394 $1.058.334.000,00200 $2.084.917.980,00
Company F $3.500.000,0023 $3.253.000,0021 $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!!!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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

Thanks for your help 🙂