Rules: Nested ATTRS()

[Eric]

Maybe I am missing something due to the blood in my eyes from repeatedly banging my head against the wall. Have I mentioned how helpful I find the error messages!

Goal: Check prior period Year To Date total to see if it >= 98,910 . True = 0 False = Wages * 0.062 (For all of you in the USA we love the tax known as FICA)

Code: Select all

['601910'] = N: IF( ['601910',ATTRS('Month',ATTRS('Month',!Month,'Prior Period'),'Short Name')|" YTD"]>=98910
                           ,0
                           ,(['601310']*0.062);

Month Dimension Detail
Element, Prior Period, Short Name
1, 12, JAN
2 , 1 , FEB
3 , 2 , MAR
4 , 3 , APR
5 , 4 , MAY
6 , 5 , JUN
7 , 6 , JUL
8 , 7 , AUG
9 , 8 , SEP
10 , 9 , OCT
11 , 10 , NOV
12 , 11 , DEC
FEB YTD,
MAR YTD,
Etc


Excuse the formatting... don't know what to do to fix it?

[Michel Zijlema]

Hi Eric,

In your rule you mix an internal ( type ['...'] ) reference with a rules function. This is not possible. If you want to apply a function you need to use the external notation ( type DB(cube, ..., '601910', ..., AttrS(...), ...) ).

Michel

[Eric]

I also tried

Code: Select all

['601910'] = N: IF( DB('2009 HC Expenses',!2009 Budget HC,'601910',ATTRS('Month',ATTRS('Month',!Month,'Prior Period'),'Short Name')|" YTD")>=98910
                           ,0
                           ,(['601310']*0.062);

[Eric]

Michel... man you are quick. Did even have enough time to mention that I tried what you suggested. Thanks

[TomBr]

Hi Eric,

From your list "Short Name" appears to be JAN, FEB, etc so it must be set up as a text attribute or alias. Therefore should your test not be
@>= '98910'
rather than
>= 98910
though I confess I don't really understand where the 98910 is coming from.

Tom

[Eric]

@>= '98910'
rather than
>= 98910
though I confess I don't really understand where the 98910 is coming from.

Maybe I have been unclear. I will try to explain this a little better

I do not want the total YTD expense for GL account '601910' to exceed $98,9810

Formula Break down of what I want

GL Account FICA '601910' = N: IF (Prior Period YTD Amount >= $98,9810, 0 , GL Account Wages '601310' * Taxe Rate 6.2%

IS that a better explanation of what I am trying?

Also I am using the nested ATTS() to do the following

!Month = 6
Prior Period = 5
5 Short Name = MAY
Join MAY | " YTD" = MAY YTD

'MAY YTD' is a Valid Alias

[Michel Zijlema]

Hi Eric,

The DB rule looks OK to me. I wonder though whether the spaces in the name of the '2009 Budget HC' dimension is causing problems.
Maybe replacing !2009 Budget HC with !'2009 Budget HC' will help? (I'm not sure, I never use dimension or cube names with spaces in it).

Michel

[Michel Zijlema]

Hi Eric,

after re-reading your rule I noted that you use the literal " YTD". This should be with single quotes (so ' YTD').

Michel

[Eric]

That did it. You Da Man!

[Steve Vincent]

Hi Eric,

The DB rule looks OK to me. I wonder though whether the spaces in the name of the '2009 Budget HC' dimension is causing problems.
Maybe replacing !2009 Budget HC with !'2009 Budget HC' will help? (I'm not sure, I never use dimension or cube names with spaces in it).

Michel

We use dim names with spaces all the time, it never causes an issue (just in case anyone was wondering!)

[ScottW]

Hi Eric,

Looks like your problem has been solved by single quotes

I'm no expert on US GAAP but to optimize your rule and make the model easier to maintain you might want to consider two things

1/ hold the threshhold value of 98910 and the tax rate of 0.062 in a control cube so they are not hard coded in the rule for when the tax legislation changes as it inevitably does and some poor sod who doesn't know what a TM1 rule is has to update the system

2/ for a single dimension simple multiplication you could also use a consolidation weighting to calculate the tax and then the rule would be a lookup of a value rather than doing the calc (might be marginally ever ever so slightly more efficient, ... but probably would not make up for looking up 2x DB() ref rather than a hard coded values.)

[Eric]

I'm no expert on US GAAP

This is a good thing since now the US is talking about moving away from GAAP and to the international standard. Guess I will have more things to add to my list useless knowledge.... Anyone need help with there COBAL?

1/ hold the threshold value of 98910 and the tax rate of 0.062 in a control cube so they are not hard coded in the rule for when the tax legislation changes as it inevitably does and some poor sod who doesn't know what a TM1 rule is has to update the system

Already on it. I had a control cube setup, but since the rule wasn't working I was eliminating as many variables a possible to help troubleshoot.

2/ for a single dimension simple multiplication you could also use a consolidation weighting to calculate the tax and then the rule would be a lookup of a value rather than doing the calc (might be marginally ever ever so slightly more efficient, ... but probably would not make up for looking up 2x DB() ref rather than a hard coded values.)

Didn't think of that...... interesting.

[osteveo]

Another consideration is that the FICA % cap is 6.2% on wages of 97,500 in 2007 (102,000 in 2008), but this cap is applied on an employee by employee basis.

So, if 2 individual wages make up that account and each earn 50000 for a total of 100000 your formula would return 0 when in fact it should be 6200 (50000 * 6.2%)*2.