Average calculation using TI process

[CRP0021]

Hi all,

Just looking for some advice on the best way to write a TI that is calculating historical averages based on a range of periods, then inserting that calculated value to a specified future month.

Here are some more details:

I have 2 parameters in my TI process ( pAvgYrStart and pAvgYrEnd).

These values can be any range of years but for this example we’ll use 2021 to 2023.

I also have 2 parameters which define the forecast start and end period ( pFcstStartPeriod and pFcstEndPeriod). This is to tell the TI which periods to paste the values against. For this example we’ll use Jan-2024 to Dec-2024.

The average calculation is quite simple in that if the user inserts a range of 3 years as in the example above, the process needs to look back at the 3 previous January months, sum them together and divide by 3. Then insert this value into Jan-2024. And the same goes for all periods in the Forecast start to end range.

I am able to calculate the denominator of this equation whether then users inputs 3 years or 10 years by just subtracting the pAvgYrEnd from pAvgYrStart but I’m running into a mental block with the numerator part of this equation.

Where I’m getting stuck is accumulating the values in the numerator and then inserting them to the corresponding future period. Jan-2021 + Jan-2022 + Jan-2023 / 3 should be placed into Jan-2024 and so on for all the rest of the periods.

Any advice or thoughts on how to approach this would be greatly appreciated.

Thanks in advance!

[gtonkin]

Why not create a C level, something like Average Periods, add the periods as children with a weighting of 1.
You will then have an aggregate value and can count the number of children using ELISCOMP.

Also, if you derive the number of periods first, your weighting could be 1/no. periods and then the C level is the average.

Another approach would be to create an MDX view where you could assign the periods to a set and use sum to create a new member, then process the summed column in your TI.

[declanr]

Looking at the requirement I would probably be inclined to just go with a couple of while loops as a simple implementation; then check that the performance is good/acceptable - if its not I would investigate other options.

I gather from your query that if the target range is Jan - Dec; the Jan should only take the average of previous Jans and Feb would take the average of only previous febs etc.

Something like below would probably work fairly quickly if it is already specified to a smaller intersection of the cube; if it was also iterating over large numbers of elements from other dimensions then looking at alternative solutions which would leverage the consolidation algorithm would probably be beneficial.

Code: Select all

sTargetPeriod = pFcstStartPeriod;
sEndPeriod = pFcstEndPeriod;
While ( sTargetPeriod @<> '' );

	sTargetMonth = SubSt ( sTargetPeriod, 1, 3 );
	
	# Calculate Value
	nTotValue = 0;
	nTotCount = 0;
	sSourceYear = pAvgYrStart;
	While ( sSourceYear @<= pAvgYrEnd );
	
		sSourcePeriod = sTargetMonth | '-' | sSourceYear;
		nTotValue = nTotValue + CellGetN ( <CubeName>, sSourcePeriod,... <Other Elements>, 'MeasureName' );
		nTotCount = nTotCount + 1;
		
		sSourceYear = AttrS ( <YearDimName>, sSourceYear, 'NextYear' );
	End;
	
	nAverage = nTotValue \ nTotCount;
	
	CellPutN ( nAverage, <CubeName>, sTargetPeriod,... <Other Elements>, 'MeasureName' );

	# Move to next period (or set to blank and end loop if end period has been reached)
	If ( sTargetPeriod @= sEndPeriod );
		sTargetPeriod = '';
	Else;
		sTargetPeriod = AttrS ( <PeriodDimName>, sTargetPeriod, 'NextPeriod' );
	EndIf;

[CRP0021]

Thank you both for the feedback!
I've used both suggestions to come up with a solution that works well.
Much appreciated!