Nested IF Statements

[Beast]

Wondered if anybody could offer some advice ?

I am attempting to write a conditional formula that calculates a value for Payroll Tax % dependent on salary (ie Salary =0 THEN Payroll Tax % = 0, Salary <= 20000 THEN Payroll Tax % = 24%, Salary > 20000 THEN Payroll Tax % = 35%) and have come up with the following which seems to work but I am wondering if I can perhapsmake it more elegant and nest the conditional formulas but cant seem to get the syntax to work for me. Or is my approach sensible ?

['% Payroll Tax']=N:(IF(['Salary']<=20000,0.24,CONTINUE));
['% Payroll Tax']=N:(IF(['Salary']>20000,0.35,CONTINUE));
['% Payroll Tax']=N:(IF(['Salary']=0,0,CONTINUE));C:['Payroll Tax']\['Salary'];

My second issue is that I am then trying to calculate an appropriate weighted average for the consolidation levels and cant get the rule to apply the consolidation level rule.

Thanks as ever.

Beast

[lotsaram]

Rather than hard-coding both the salary bands and applicable tax rates in the rule itself it would be a much better design to hold the salary bands and tax rates in a small lookup cube and do the tax rate determination there.

If you set this up correctly there will be no need for IF statements in your main cube.

Also much better for maintenance and scenario analysis. IMO hardcoded values should always be avoided whatever the tool.

[Andy Key]

I agree with lotsaram about not having hardcoded values in a rule, but if you must...

Code: Select all

['% Payroll Tax'] = N:
   If( ['Salary'] > 20000,
      0.35,
      If( ['Salary'] > 0,
         0.24,
         0));
   C:['Payroll Tax'] \ ['Salary'];

I'm assuming you don't pay anyone a negative salary.

As for why your C: rule wasn't firing, I think that it is because you are not using separate N: and C: level rules, and as you were using more than one rule line to reference the same data area TM1 will expect the C: to be on the first line. Also, the N: side of your last line would never have fired as the first condition is Salary < 20000, so anyone who has a Salary of 0 will have fired that rule and have a % Payroll Tax of 0.24 rather than 0. If you moved your last line up to the top, then the C: rule would have kicked in and Salary of 0 would have a % Payroll Tax of 0.

[Beast]

Many thanks for your help guys, managed to get it to work now, just couldnt get the syntax to work quite right. And I will investigate the suggestion of having a look-up cube but just wanted to get the hard coding to work first. New to TM1 and trying to convert Cognos Planning models.

My next dilema is how to test that two string measures fields have both been populated. In Planning I would write something along these lines

['Monthly Payroll'}=N:IF({'Cost Centre'] > 0 AND ['Grade'} > 0, {'Total Payroll'] / 12, 0);

All the documentation suggests I replace the AND with & but rules editor does not sem to like that.

As always any help greatly appreciated.

Beast

[Steve Rowe]

Hi Beast,
When writing a reference to strings you have to fully qualify the reference using the DB statement, additionally the test for equivalence is different

['Monthly Payroll'}=N:IF(
#If not ( both string measures = blank) note that the ~ is the logical not
~ ( DB ( cubename, !dimrefs , 'CostCentre')@='' & DB (cubename, !dimrefs , 'Grade')@='' ),
['Total Payroll'] / 12, 0);

#{'Cost Centre'] > 0 AND ['Grade'} > 0, {'Total Payroll'] / 12, 0);

[Beast]

Steve

Thank you very much indeed, you are a gentleman and a scholar. I changed the & to a % and it worked precisely as I had hoped.

I think your opening point "When writing a reference to strings you have to fully qualify the reference using the DB statement" was particularly relevant.

Thanks.

Beast