Welcome to the new CogKnowHow.com!


If you have trouble logging in, reset your password via the login window
by clicking the 'Forgot Password' link.

Previous month

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.