TM1 Forum

How do would I create a rule with an element that has a picklist and use the picklist Attrn?

This is the calculation I started with
['Total fees' ] =N: IF (['Maturity Terms']>0,(['Loan Origination Fee' ]
+['Other Loan Fees' ] )\['Price Test' ] ,0);

Maturity terms is a string and only works with my links between cubes to look up other data.

Any rule I write with the Maturity terms does not work because it is a string and needs to be converted to numeric.

Maturity Terms is a picklist that looks at the Term_Years dimension and it has a numeric attribute called TermsNumeric.

I started down the path writing this:

['Total fees' ] = N: IF(ATTRN('Commerical_Pricing' , !Maturity Terms, 'TermsNumeric')>0, (['Loan Origination Fee' ]
+['Other Loan Fees' ] )\['Price Test' ] ,0);

But it is looking for the element attribute for Maturity Terms not Term_Years which is the picklist. Any suggestions on how to write this an use the picklist numeric attribute.
Or is there a better way to convert a string to numeric with a picklist element?

Thanks

Hi,

As you note, the issue is not the rest of the rule, but the evaluation of the condition

IF (['Maturity Terms']>0

Can I clarify a couple of things?
1/ Is ['Maturity Terms'] an element in the cube - most likely in the measures dimension, and confirming it is a string element.
2/ Is ['Maturity Terms'] a dimension in its own right?

It looks also like the value of ['Maturity terms'] is not hugely relevant to the outcome - whatever its value other than zero does not change the calculation. If that is so, maybe reword the original calculation to use a string evaluation - if Maturity Terms is blank, then zero, else calculate.
['Total fees' ] =N: IF (['Maturity Terms'] @='', 0,
(['Loan Origination Fee' ]+['Other Loan Fees' ] )\['Price Test' ]);

However it looks like the issue may be that you are using a dropdown value (picklist) in the cube, and would need ['Maturity Terms'] to be an element in the dimension 'Commercial Pricing' as well as a dimension in its own right. Try replacing the !Maturity Terms with a DB reference for the element, as this should sort out some of the reference issues.

Overflow.

What Overflow said is ½ right. To test string values you need the @ operator. However to refer to string values you cannot use the shorthand square bracket notation, you must use the full DB() reference as you would for calling an external cube value. To work the test should be
IF( DB( 'your cube', ...., 'maturity terms' ) @<> '0',

Thanks Lotsaram

Thought I had said that in the last part, but worth the clarification...

overflow.

Thank you that worked!