TM1 Forum

Posted: **Mon Aug 04, 2014 6:57 am**

Hi experts,

I am working on calculating a 12 month average for the last 12 months from the current period.
My time hierarchy looks like:
-(Year)
-- Q12014
--- Jan14
--- Feb14
--- Mar14
-- Q22014
and so on..

When I am checking the feeder at the Consolidated level (for example: 'All Regions'), it shows 'not fed'
I wasn't sure where else the value needs to be fed.

Here I am posting the sample code for the above calc:

Code: Select all

skipcheck;
['12MonthAvg'] =N:
if(dimix('Time Period', !Time Period)<=12,Stet,
(DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-1), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-2), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-3), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-4), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-5), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-6), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-7), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-8), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-9), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim, 'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-10), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim,'Revenue_meas1')+
DB('TestCube1', !Version,Dimnm('Time Period', Dimix('Time Period', !Time Period)-11), !Entity, !Region, !Domain, !NCC,
 !EleGroup, !Account, !CurrencyDim,'Revenue_meas1')+
)\12);


feeders;
['Revenue_meas1'] => ['12MonthAvg'];

Let me know if I m missing anything..
Do i need to feed the period as well to the calculated field? ( I don't think its needed

)

Posted: **Mon Aug 04, 2014 7:29 am**

It would have an average if any one of the last 12 months have a value, even if the month itself doesn't... so yes you also need to feed by month.

Posted: **Mon Aug 04, 2014 7:43 am**

Thanks for the reply.

I have written the following feeder, but still shows incorrect values (only at consolidation)

Code: Select all

['Revenue_meas1'] => DB('TestCube1', !Version,if(dimix('Time Period', !Time Period)<=12,DNEXT('Time Period',!Time Period),stet), !Entity, !Region, !Domain, !NCC, !EleGroup, !Account, !CurrencyDim, '12MonthAvg');

Please correct me if 'm wrong

Posted: **Mon Aug 04, 2014 8:46 am**

Hi,

You can do this using consolidations in your time dimension and avoid having to write rules/ feeders at all. A simple TI process can be used to maintain this.

All 12MonthAvg

Jan14 12MonthAvg

Jan14 (weighting 1/12)
Dec13 (weighting 1/12)
Nov13 (weighting 1/12)
Oct13 (weighting 1/12)
Sep13 (weighting 1/12)
Aug13 (weighting 1/12)
Jul13 (weighting 1/12)
Jun13 (weighting 1/12)
May13 (weighting 1/12)
Apr13 (weighting 1/12)
Mar13 (weighting 1/12)
Feb13 (weighting 1/12)
Feb14 12MonthAvg

Feb14 (weighting 1/12)

Feb14 (weighting 1/12)
Jan14 (weighting 1/12)
Dec13 (weighting 1/12)
Nov13 (weighting 1/12)
Oct13 (weighting 1/12)
Sep13 (weighting 1/12)
Aug13 (weighting 1/12)
Jul13 (weighting 1/12)
Jun13 (weighting 1/12)
May13 (weighting 1/12)
Apr13 (weighting 1/12)
Mar13 (weighting 1/12)

This way you will get a 12 month rolling average for ALL measures.

Posted: **Mon Aug 04, 2014 9:09 am**

Thanks for the reply Dan.
But it is not always constant in my situation

For the current year (2014), i would need to calcluate 12 month average
For previous periods (2013 and before...), I would require to calculate an average of 13 months (rolling periods) at any particular time period

So, it is not feasible for me to give weights accordingly (since it is not constant like 12 months a year)
Also, as declan replied, its difficult to write feeders feeding for every month as well..

Posted: **Mon Aug 04, 2014 11:48 am**

The way I've normally handled this in the past is to have a lookup cube. Within the lookup cube have +1,+2, -1, -2, etc upto the number of months that you need to roll back / forward. You can then feed so many forward or back. This however does lead to some over feeding so you may want to test it in a UAT environment to get better grip of performance / memory impact,

Jim.

Posted: **Mon Aug 04, 2014 2:12 pm**

Why don't you use TI for this?

Posted: **Mon Aug 04, 2014 3:22 pm**

Having spent 20+ years working in the banking industry I understand all the nuances of them I can tell without a doubt the way Dan.Kelleher told you to do it is the FAR superior way to do it. No feeders and the performance will be light years ahead of doing it via rules. There are a few "quirks" to doing it, like having different weights for leap years and the 12 versus 13 month scenario that you have but, they are solveable by adding extra consolidation nodes in the Time dimension to cover all the quirks and then directing reports/view to the correct time periods, based on what the user is looking for.

Posted: **Tue Aug 05, 2014 5:34 am**

Well.
Thanks for all the replies..
Just wanted to post my result which was so far looking 'successful'

The following is the feeder which i have written, with a little bit of change in my earlier feeder posted:

Code: Select all

['Revenue_meas1'] => DB('TestCube1', !Version,IF (ELLEV('Time Period', !Time Period)=0,DNEXT('Time Period',!Time Period),stet), !Entity, !Region, !Domain, !NCC, !EleGroup, !Account, !CurrencyDim, '12MonthAvg');

And so far, my values are looking good.. and even the performance as well.. (*touchwood*)

Posted: **Tue Aug 05, 2014 4:31 pm**

Thanks for posting the final resolution.Glad you resolved it.I don't know whether it is useful for you or not but anyway please go through it:

http://olapinsights.wordpress.com/2012/ ... ognos-tm1/

Posted: **Wed Aug 06, 2014 4:51 am**

Off topic slightly, per above, why use all that when you can use the MDX + Last Periods to create a "Outlook Periods" subset to do similar without feeders etc?
Bedrock have already got process to do the creating, below is an example to get last 8 days & last 12mths.

You just need to feed the Parameter to place value Date/Month variables to get your start point...

Code: Select all

MyDimDly = 'PeriodDaily' ;

# 1st Instance Variables
MySub1 = 'Sys_Last 8 Days' ;
MyVar = 8 ;

# Build MDX String with CurrDate Variable
MyMDX = 'LastPeriods(' | NumbertoString(MyVar) | ', [' | MyDimDly | '].[' | CurrDate | '])' ;

# Execute Bedrock process to re-insert Elements
Executeprocess( 'Bedrock.Dim.Sub.Create.ByMDX', 'pDimension', MyDimDly, 'pSubset' , MySub1 , 'pMDXExpr' ,  MyMDX ) ;

#============================================

# Monthly Subsets

MyDimMth = 'Period' ;

# 1st Instance Variables
MySub2 = 'Sys_Last 12 Mths' ;
MyVar = 12 ;

# Build MDX String with CurrDate Variable
MyMDX = 'LastPeriods(' | NumbertoString(MyVar) | ', [' | MyDimMth | '].[' | CurrMth | '])' ;

# Execute Bedrock process to re-insert Elements
Executeprocess( 'Bedrock.Dim.Sub.Create.ByMDX', 'pDimension', MyDimMth, 'pSubset' , MySub2 , 'pMDXExpr' ,  MyMDX ) ;

TM1 Forum

Need help for feeding rolling prior periods

Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods

Re: Need help for feeding rolling prior periods