Hi TM1 Gurus
I have a cube view like the foll
Year Dim(To select years from 2008-2010)
------------EAD(Current)----EAD(Forecast)
q1
—Jan
—Feb
—Mar
q2
—Apr
—May
—Jun
q3
—Jul
—Aug
—Sep
q4
—Oct
—Nov
—Dec
We have values for Ead(Current) and for EAD (forecast)they want to use previous month data. For jan 2010 u should have dec 2009 data(of Ead current), for feb 2010 u should have jan 2010 data(of Ead Current)
How can i do it with less rules, like i dont want to write rule for every month.Please suggest me the best way to do it.
Thanks
Daya
daya007
Posts: 10
Joined: Thu May 27, 2010 10:47 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007
All member have indexes. You can see them in the dimension editor. These indexes can be fetched with the DIMIX function. Subtract 1. wrap DIMNM to get the member itself. Also check jan or q1 in which case you pick dec previous year.
EAD (forecast) = if(!month@=‘Jan’ % !month@=‘q1’,
DB(‘ThisCube’,DIMNM(‘Month’,(DIMIX(‘Month’,!Month)-1)),‘Dec’,‘EAD(Current)’,…other dimensions…),
DB(‘ThisCube’,!year,DIMNM(‘Month’,(DIMIX(‘Month’,!Month)-1)),‘EAD(Current)’,…other dimensions…);
Should get you started at least.
br/David
This is rather risky as you won’t be able to control the index sequence unless the dimension is totally flat. Better to set up a pair of attributes Next and Prior and populate them with the correct lookups. Doesn’t take long and is reusable on your next project.