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'];