Average/Rolling Average Question

TM1N00b · Post by **TM1N00b** » Tue Oct 11, 2016 3:38 pm

Hi, all.

I am using TM1 10.2.2 64-bit

I have 2 questions:

1. I am trying to find a better way to code an increasing average over multiple fiscal years (FY) outside of creating a new line for each new FY.

Currently, I have a work-around in place until I can figure out how to code this operation more efficiently. The purpose is to create a multi-year average and populate each month (OCT - SEP) with the average of the subject month over the years.

It reads as follows:

#Calculation for Monthly Average (current logic requires years following 2020 to be added)
['Monthly Average','2013']=N: ['Monthly','2012'];
['Monthly Average','2014']=N:(['Monthly','2012']+['Monthly','2013'])\2;
['Monthly Average','2015']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014'])\3;
['Monthly Average','2016']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014']+['Monthly','2015'])\4;
['Monthly Average','2017']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014']+['Monthly','2015']+['Monthly','2016'])\5;
['Monthly Average','2018']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014']+['Monthly','2015']+['Monthly','2016']+['Monthly','2017'])\6;
['Monthly Average','2019']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014']+['Monthly','2015']+['Monthly','2016']+['Monthly','2017']+['Monthly','2018'])\7;
['Monthly Average','2020']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014']+['Monthly','2015']+['Monthly','2016']+['Monthly','2017']+['Monthly','2018']+['Monthly','2019'])\8;

Again, my goal is to avoid having to add a new line of logic every single FY. Can anyone assist?

2. Is it possible to code more efficient logic for a rolling average? Same elements as above, but for example, a rolling average of 5 FYs. When a new year begins (assuming it is created and selected in a selection cube), the first year should automatically drop off.

I appreciate everyone's time/help on this.

Ben

declanr · Post by **declanr** » Tue Oct 11, 2016 4:14 pm

I would scrap the rules altogether for this and just use consolidations with the children weighted depending on the number of components.

lotsaram · Post by **lotsaram** » Tue Oct 11, 2016 4:20 pm

declanr wrote:I would scrap the rules altogether for this and just use consolidations with the children weighted depending on the number of components.

ditto

Wim Gielis · Post by **Wim Gielis** » Tue Oct 11, 2016 4:29 pm

Absolutely. And Turbo Integrator could help you automate this creation of elements and setting of weights.

TM1N00b · Post by **TM1N00b** » Tue Oct 11, 2016 4:43 pm

Can you elaborate on which elements you would make children and which you would make consolidated?

Also, this particular cube has several functions across multiple elements, so I would think creating consolidations would skew some of the elements without the average/rolling average calculations.

I'll try to describe the cube a little better.

The cube has the following elements that span across Month and FY elements as well as Obligations and Liquidations (Obs and Liqs do not affect the logic at all):

'Authority' (This is a manual input or STET element that performs no calculations)
'Cumulative' (This is a manual input or STET element that performs no calculations)
'Cumulative Average' (Similar to original calculation described in first post)
'Monthly' (Calculation derives figure based on 'Cumulative' [October subtracted from November])
'Monthly Average' (Original calculation described in first post)

I hope this provides some clarity.

Ben

declanr · Post by **declanr** » Wed Oct 12, 2016 6:12 am

The consolidations would have to be alongside a redesign of structure. As you can see the design you have can start leading to unneccessary complexity.

That being said if you are adamant on sticking with it; then its not TM1 that helps youbbut just rethink the logic of how you approach the rule e.g.

Code: Select all

['Average']=N:
(
(
Db('SameCube',!Dim1,!Dim2,...,AttrS('Year',!Year,'Prior_Year'),'Average')
* ( Numbr(AttrS('Year',!Year,'Prior_Year'))-2012))
+
['Monthly']
)
/
(Numbr(!Year) - 2012);

Obviously this is just an idea; you shouldn't hard code 2012 in and I have written this on the phone while myvtrain is delayed at a red light so I apologise for any typo coding errors.
The only requirement is that you have a prior year attribute for the period... which if you don't already have you should put in anyway as they come in very handy.

Mark RMBC · Post by **Mark RMBC** » Wed Oct 12, 2016 11:17 am

That is a smart solution Declan. Just one thing,

doesn't this part of the code:

+
['Monthly']

need to be something like

Db('SameCube',!Dim1,!Dim2,..,AttrS('Year',!Year,'Prior_Year'),'Monthly')

i.e. the actual from the previous month of the previous year.

Also, I think the base year (2012) need to point to the actual value and any years after this pick up your logic?

regards, Mark

declanr · Post by **declanr** » Wed Oct 12, 2016 5:40 pm

Mark RMBC wrote:That is a smart solution Declan. Just one thing,

doesn't this part of the code:

+
['Monthly']
need to be something like

Db('SameCube',!Dim1,!Dim2,..,AttrS('Year',!Year,'Prior_Year'),'Monthly')

i.e. the actual from the previous month of the previous year.

Also, I think the base year (2012) need to point to the actual value and any years after this pick up your logic?

regards, Mark

Absolutely correct; it was a very quick idea to point in the right direction as opposed to a worked solution.
My preference would still be a redesign though; as although our information here is limited, it seems like a sub-optimal design - the direction that the code I provided points in; allows for a reduction in lines of code but it is very messy still...

However without all the details everything must be caveated - we have all done things that are "sub-optimal" once or twice for various reasons.

TM1N00b · Post by **TM1N00b** » Thu Oct 13, 2016 4:49 pm

Gentlemen - first, thank you for investing time and thought into my problem. I tried implementing the logic that you provided and I'm getting skewed numbers (see attachment)

Again, please don't take my tone to be ungrateful or all-knowing (I am definitely neither), but the logic you provided doesn't coincide (at least to me) with the typical average formula that one would employ.

Layman's terms what was provided:

Average =

((Prior Year Average * Prior Year - 2012) (i.e. 2014 - 2012 = 2)
+
Prior Year Monthly )
/
Year Number - 2012 (i.e. 2014 - 2012 = 2)

It's possible that I am misinterpreting what you supplied above, but I've always been familiar with the following average calculation:

Average =

Sum: All months (by month) of prior years

/

Number of prior years

I've attached a snapshot of the cube as well as the rule file.

declanr · Post by **declanr** » Thu Oct 13, 2016 5:11 pm

Right click on one of the calculated cells and select trace calculations to see what is happening.

The code was meant to be amended somewhat and just provide an idea; Mark mentioned in his post 2 of the amendments you should have made - the below is a pointer but will still need some work. If you keep tracking calculations as you make amendments it is much easier to follow.

Code: Select all

['Monthly Average']=N:
(
	( 
		DB ( '1002 Actuals', AttrS ( 'FY', !FY, 'Prior Year' ), 'Monthly Average', !1002 LI, !OBL LIQ, 'Amount' )
		*
		( Numbr ( AttrS ( 'FY', !FY, 'Prior_Year' ) ) - 2011 )
	)
	+ DB ( '1002 Actuals', AttrS ( 'FY', !FY, 'Prior Year' ), 'Monthly', !1002 LI, !OBL LIQ, 'Amount' )
)
\ 
(
	Numbr ( !FY ) - 2011
);

Mark RMBC · Post by **Mark RMBC** » Fri Oct 14, 2016 8:02 am

I have attached an Excel file which hopefully illustrates the point.

The logic goes back to your first post, i.e.

['Monthly Average','2013']=N: ['Monthly','2012'];
['Monthly Average','2014']=N:(['Monthly','2012']+['Monthly','2013'])\2;
['Monthly Average','2015']=N:(['Monthly','2012']+['Monthly','2013']+['Monthly','2014'])\3;
Etc;
Etc;

The crucial point to bear in mind is that the logic provided by declan would only work from 2013 (the Excel attachment says after 2013 but actually it is 2013 and beyond!!), assuming the year is 2011. So you need a few if statements to deal with 2011 and 2012.

Hopefully the attached Excel makes sense!

If I have misunderstood your requirement in anyway I apologise in advance!

regards, Mark

TM1N00b · Post by **TM1N00b** » Mon Oct 17, 2016 12:27 pm

All, again, thank you for the tips. I haven't been able to do much with them, but they are appreciated nonetheless.

My client recently changed his mind in favor of a 5 year rolling average.

I set up the FY dimension with an attribute to count FYs as well designate a "Prior Year" for each year (i.e. 2011's prior year is 2010).

: Attributes; Attributes.PNG (16.87 KiB) Viewed 9583 times

I started to re-work the logic with the following:

Code: Select all

['Monthly Average']=N:

	IF (ATTRN('FY', !FY, 'Count')<=6,

		DB('1002 Actuals', ATTRS('FY', !FY, 'Prior Year'),!1002 LI, !OBL LIQ, !Months for FY 1002, 'Monthly', 'Amount')+

		DB('1002 Actuals', !FY,!1002 LI, !OBL LIQ, !Months for FY 1002, 'Monthly', 'Amount'),

STET);

What I am trying to figure out is how to get the 5 prior years to add and get divided by 5 to create the 5 year rolling average. So far, with the code above, I am getting the current year 'Monthly' + the prior year 'Monthly' only. I do not want the current 'Monthly' to be included - only the 5 prior 'Monthly's

Mark RMBC · Post by **Mark RMBC** » Mon Oct 17, 2016 1:48 pm

I am amazed that was the logic you ended up with given the advice, but how about the following (albeit you may need some modification):

['Monthly Average']=N:

If(ATTRN('FY', !FY, 'Count')<=2,

DB('1002 Actuals', ATTRS('FY', !FY, 'Prior Year'),!1002 LI, !OBL LIQ, !Months for FY 1002, 'Monthly', 'Amount'),

IF (ATTRN('FY', !FY, 'Count')>2 & ATTRN('FY', !FY, 'Count')<=6,

(DB('1002 Actuals', ATTRS('FY', !FY, 'Prior Year'),!1002 LI, !OBL LIQ, !Months for FY 1002, 'Monthly Average', 'Amount') *

(Numbr(ATTRS('FY', !FY, 'Prior Year')) - 2011) +

DB('1002 Actuals', ATTRS('FY', !FY, 'Prior Year'),!1002 LI, !OBL LIQ, !Months for FY 1002, 'Monthly', 'Amount')) /

(Numbr(!FY) - 2011),

STET));

** Just an update. Given you have no value for 2011, then the average for 2012 would be 2012 monthly amount or whatever and 2013's average would just be the monthly amount from 2012 and therefore the logic above would start from 2014, i.e. when ATTRN('FY', !FY, 'Count')>3. So you would need to change 2011 to 2012 and you would have to modify each of the ATTRN('FY', !FY, 'Count') parts of the code. Also to make this code dynamic you would need make this

& ATTRN('FY', !FY, 'Count')<=6

refer to the current year rather than the attribute.This way you don't have to change the code year on year

tomok · Post by **tomok** » Mon Oct 17, 2016 2:37 pm

What you really need is not to have separate dimensions for Year and Month. I know it makes reporting easy but it can make time-based calculations a nightmare. This would be a piece of cake if you only had one time dimension, it could all be done in the dimension hierarchy with no rules. It would be lightning quick and not require any feeders.

TM1N00b · Post by **TM1N00b** » Mon Oct 17, 2016 6:29 pm

Mark - you're a genius. I'm still not 100% keen on exactly what your logic is doing, but I ran it against my "longhand" logic and it works. Thank you for your help - I really appreciate it!

Tom - The model I inherited has a few hundred cubes in it. All are set up with separate FY and Month dimensions. This is one of those "it is what it is" situations.

Thanks again to anyone who put in time and effort on my problem! I'm grateful!

Ben

TM1 Forum

Average/Rolling Average Question

Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question

Re: Average/Rolling Average Question