Rules and nesting DB

[jameswebber]

Guys,
I'm trying to write a rule that takes adjustments but only puts these into a start year and future years specified in an assumptions cube

I have some elements in my assumptions:

• ‘Assumptions','Forecast','No year','Full year','Planning Cube Base year' = 2012 (sting value)
  ‘Assumptions','Forecast','No year','Full year','Planning Number of Years = 3 (numeric)

I have written a rule like below


['Current Plan','Adjustment to Reforecast $'] = N:

Code: Select all

# If Planning year ignore (STET)
	IF(!Year @= DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'),STET, 
# If year larger than planning year smaller than planning year plus number of plan years populate from planning year amount, otherwise the value is zero
	IF(((DIMIX('Year',!Year)> DIMIX('Year',DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year')))
	& (DIMIX('Year',!Year)<= DIMIX('Year',DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'))+ DB('Assumptions','Forecast','No year','Full year','Planning Number of Years'))
	),  ['Current Plan', year:'2012', 'Adjustment to Reforecast $'],
	0
	)
	);	

This works fine but the issue is the I need to hardcode the Adjustment to Reforecast $ data I’m flowing into it. Currenlty hard set to 2012.

['Current Plan', year:'2012', 'Adjustment to Reforecast $']

Ideally I would like to dynamically apply this
Something like this but I just can’t seem to get my syntax correct. Is this possible or am I way wrong here?

Code: Select all

 ['Current Plan','Adjustment to Reforecast $'] = N:
	IF(!Year @= DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'),STET, 
	IF(((DIMIX('Year',!Year)> DIMIX('Year',DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year')))
	& (DIMIX('Year',!Year)<= DIMIX('Year',DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'))+ DB('Assumptions','Forecast','No year','Full year','Planning Number of Years'))
	), ['Current Plan', year: DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'), 'Adjustment to Reforecast $'],
	0
	)
	);	

[Wim Gielis]

Hello James

Your notation:

['Current Plan', year:'2012', 'Adjustment to Reforecast $']

is a simpler form of the more generic DB formula to relate a cube to another cube.

So you can just use the DB function instead of the [] notation, whereby it is not a problem to have a second DB nested within this DB
(to avoid hardcoding that Year element).

Wim

[jameswebber]

Thanks Wim,
I figured it should be possible.
The 3 elements I was trying to select were:

• Version: hard coded within my new cube = 'Current Plan'
  Year: dynamically from the assumptions cube = DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year')
  Value: hard coded within my new cube = 'Adjustment to Reforecast $'

I think the issue was that I had a account and Budget centre dimension that were causing it to be ambiguous

Maybe it's me but I couldn't specify that that my DB value from assumptions was my year dimensions and get it to work.

DB('plan_GL','Current Plan', year: DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'), 'Adjustment to Reforecast $')

So in the end I needed to specify all the dimensions, (Plan_GL being the name of my new cube):

DB('plan_GL','Current Plan', DB('Assumptions','Forecast','No year','Full year','Planning Cube Base year'), !plan_budget_centre, !plan_account, 'Adjustment to Reforecast $'),

[Wim Gielis]

Yes, that's correct.

You will also want to review the DIMIX logic, as it assumes a certain ordering of elements in the Year dimension.
Normally the ordering will not change, but you never know.
It would be safer to usr Str to convert to numbers and then do simple calculations.

[tomok]

I always deploy time dimensions in my models that have a number of attributes such as Next_Period, Next_Year_Period, Last_Period, etc., to use for lookups. These work great in rules and also in reports where I want the prior year or prior month column to be dynamic. It also means I don't have to worry about the customer breaking the model by adding a new period and somehow breaking the rules because they added the new periods to the beginning of the period instead of the end or vice versa.

[jameswebber]

Thanks for the advice guys on avoiding potential issues with DIMIX.
I read a good explanation of this in the TM1 cookbook that you reviewed Wim.

My real gotcha was that although I was specifying the dimension (year:) I couldn't then do a DB to get the value.
So my only way round this was to specify all the dimensions as I have done.

Thanks for everyone's help