Page 1 of 1
Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 7:19 pm
by 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?
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 7:32 pm
by 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
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 7:35 pm
by 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);
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 7:38 pm
by Eric
Michel... man you are quick. Did even have enough time to mention that I tried what you suggested. Thanks
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 7:45 pm
by 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
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 7:58 pm
by 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
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 8:01 pm
by 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
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 8:16 pm
by 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
Re: Rules: Nested ATTRS()
Posted: Tue Jun 24, 2008 8:57 pm
by Eric
That did it. You Da Man!
Re: Rules: Nested ATTRS()
Posted: Wed Jun 25, 2008 7:23 am
by Steve Vincent
Michel Zijlema wrote: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!)
Re: Rules: Nested ATTRS()
Posted: Wed Jun 25, 2008 11:21 am
by 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.)
Re: Rules: Nested ATTRS()
Posted: Wed Jun 25, 2008 12:58 pm
by 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.
Re: Rules: Nested ATTRS()
Posted: Wed Jun 25, 2008 3:06 pm
by 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.