Average of last 12 months excluding months which dont have d

Post Reply
ravitm1
Posts: 3
Joined: Mon Jan 05, 2015 6:46 am
OLAP Product: cognos
Version: 9.5
Excel Version: 10

Average of last 12 months excluding months which dont have d

Post by ravitm1 »

Hi

i want to calculate the last 12 months avgerage .but i dont want to hard code the denominator 12 .if i have data for 5 months then my denominator shuold be 5 .
how to implement .suggest me ideas .

Thanks in advance
Ravi

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'];
dan.kelleher
Community Contributor
Posts: 127
Joined: Wed Oct 14, 2009 7:46 am
OLAP Product: TM1
Version: 9.4
Excel Version: 11
Location: London

Re: Average of last 12 months excluding months which dont ha

Post by dan.kelleher »

There are a few considerations here.

If you keep track of how many months you've loaded for each year in a separate cube, you can reference this denominator using a DB() function in place of your hard-coded 12.

Alternatively you can create a new measure to keep track of whether Revenue_meas1 is non-zero via a simple rule, and divide by this. One consideration here is that a revenue might be zero because there is no revenue for this data point for this month, where revenue exists for other months. You probably want to include this month in your denominator calculation, which is why the above method is probably better suited to your needs.
ravitm1
Posts: 3
Joined: Mon Jan 05, 2015 6:46 am
OLAP Product: cognos
Version: 9.5
Excel Version: 10

Re: Average of last 12 months excluding months which dont ha

Post by ravitm1 »

I wrote rule bt could not get the exact count of months which have data

rule

[count]=N:if(['Revenue_meas1'] <> 0 ,1,0);
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Average of last 12 months excluding months which dont ha

Post by declanr »

ravitm1 wrote:I wrote rule bt could not get the exact count of months which have data

rule

[count]=N:if(['Revenue_meas1'] <> 0 ,1,0);
In what sense does it not give you the count?

Either way though assuming this is just actuals data from a source system and not being constantly changed by the users why don't you load the average via TI as well. There is no point in using a memory intensive rule when the data won't change and at the time of running you would know how many months to loop back so that could be a simple parameter.
Declan Rodger
ravitm1
Posts: 3
Joined: Mon Jan 05, 2015 6:46 am
OLAP Product: cognos
Version: 9.5
Excel Version: 10

Re: Average of last 12 months excluding months which dont ha

Post by ravitm1 »

Hi ,

Thanks for the reply .

Yes i am loading actuals data from a source system .and the data is not changing

I am tm1 beginer .could you guide me how to write ti for average and how to loop back to last 12 months using parameter .
Post Reply