Moving Average Rule

[LORR]

Good morning/afternoon TM1 gurus,

I'm currently working on a sales project that requires a moving average rule. Dimensions are "customer" , "Location" , "Measures" , "Year" such as 2011,2012,2013 and
"day" with the 365 days of the year that rolls up to the corresponding month as parent (Jul, Aug, Sep...) and the month to the top parent "Total Year" (see example below)

Dim.png (616 Bytes) Viewed 7350 times

I'm trying to show the sales daily average value based on a 10 day moving period, for example see below

Avrg.png (16.93 KiB) Viewed 7350 times

This means that May-15 shows the avrg of the values from May-15 to May-02 and so on...

So my question is:
How do I work out the avrg backwards based on the previous 10 days that have a value against, if there is no value then exclude it. What I mean by a value is something in the cube cell even if its a zero..???

Any help, hints and/or tips will be greatly appreciated...

[winsonlee]

Not too sure if it can be done via rules as you are looking to skip days which is 0.
I tried using TI process to do it and it is achievable. Create a view of all days at N element level and years and set "Skip Zero /Blank values".

Code: Select all

temp1 = temp2;
temp2 = temp3;
temp3 = temp4;
temp4 = temp5;
temp5 = temp6;
temp6 = temp7;
temp7 = temp8;
temp8 = temp9;
temp9 = temp10;
temp10 = Value;
CELLPUTN((temp1 + temp2 + temp3 + temp4 + temp5 + temp6 + temp7 + temp8 + temp9 + temp10)/10, 'testing4',YearX,MonthX,'Avrg JOB Val');

[declanr]

I can think of 1 way to do it via rules but its not exactly pretty.


Put an attribute against every day called "prior day" and populate it with you know what.

Create an extra measure called "Check" or something like that.

Code: Select all

['Check']=N:
   If (['Intake Value']<>0,1,0);

['Avrg JOB Val']=N:
    ( DB(Cube, dim1, dim2, !Day, 'Intake Value') + DB(Cube, dim1, dim2, Attrs('Day',!Day,'Prior Day'),'Intake Value') + DB(Cube, dim1, dim2, Attrs('Day',Attrs('Day',!Day,'Prior Day'),'Prior Day'),'Intake Value')  ....... and so on 10 times )

\

Same as above but for the "Check" measure.

You could also do it via a dimix that may look cleaner in the rule but is dependent on elements not moving.
Presuming that at least 1 of your 10 days will have a value you should probably cheat and just feed the whole measure.

A nicer way to do it possibly would be taking the values out to an intermediate cube and using consols to do the averages and then bring them back in.

[Duncan P]

When you say

What I mean by a value is something in the cube cell even if its a zero..???

I presume that you mean that zero is a significant value but that there may be days for which there is no value.

The accepted way to implement this in TM1 is to use UNDEFVALS (http://publib.boulder.ibm.com/infocente ... fvals.html) in the rule. However be aware that once you put it in the rule it will have a permanent effect on the cube and taking it out will not reverse that effect. Furthermore I do not believe that it is possible any more (9.5.2 and above) in a rule to distinguish between a cell that has a zero in it and a cell with the special undefined value. I would be delighted to be proved wrong in this. In previous versions they used to compare greater than zero but not any more.

All things considered if you are importing the data with TI it would probably be safer and more understandable to have an extra measure (e.g. "Has Data") that you populate with 1 for every sales value that you populate. Of course if the values are entered by users in the cube viewer or in Contributor you cannot do this.

[winsonlee]

Looks like a good idea. but i would like to confirm about the rules. using the rules given wouldnt it mean May-14 = (May-07 + May-08 + May-09 + May-10 + May-11 + May-14 ) / 6 ?

May-05 0
May-06 0
May-07 187,989
May-08 203,375
May-09 236,468
May-10 106,078
May-11 426,669
may-12 0
May-13 0
May-14 296,722

I can think of 1 way to do it via rules but its not exactly pretty.


Put an attribute against every day called "prior day" and populate it with you know what.

Create an extra measure called "Check" or something like that.

Code: Select all

['Check']=N:
If (['Intake Value']<>0,1,0);

['Avrg JOB Val']=N:
( DB(Cube, dim1, dim2, !Day, 'Intake Value') + DB(Cube, dim1, dim2, Attrs('Day',!Day,'Prior Day'),'Intake Value') + DB(Cube, dim1, dim2, Attrs('Day',Attrs('Day',!Day,'Prior Day'),'Prior Day'),'Intake Value') ....... and so on 10 times )

\

Same as above but for the "Check" measure.

You could also do it via a dimix that may look cleaner in the rule but is dependent on elements not moving.
Presuming that at least 1 of your 10 days will have a value you should probably cheat and just feed the whole measure.

A nicer way to do it possibly would be taking the values out to an intermediate cube and using consols to do the averages and then bring them back in.

[winsonlee]

using the rules define by declanr, I guess it is possbile if the year and the month date dimension is combine into one dimension and use attribute to define prior day according to the existance of the data but this will require alot of work to maintain the attribute as well.

[declanr]

Looks like a good idea. but i would like to confirm about the rules. using the rules given wouldnt it mean May-14 = (May-07 + May-08 + May-09 + May-10 + May-11 + May-14 ) / 6 ?

ah yes I see I mis-read the question.


I now take it as the OP always wants to average 10 days worth of data but it will only ever be the last 10 days that had values in them.

If that is the case I would still have the "Prior" attribute but populate it by a rule (I say this is what I would do... in reality I would use a TI but if a rule was essential).

The rule would be something like:

['Prior Period']=S:
If ( DB(Cube, Dim1, Dim2, Dimnm('Day',Dimix('Day',!Day)-1), 'AVG Job Val')<>0, Dimnm('Day',Dimix('Day',!Day)-1) ,
If ( DB(Cube, Dim1, Dim2, Dimnm('Day',Dimix('Day',!Day)-2), 'AVG Job Val')<>0, Dimnm('Day',Dimix('Day',!Day)-2) ,
If ( DB(Cube, Dim1, Dim2, Dimnm('Day',Dimix('Day',!Day)-3), 'AVG Job Val')<>0, Dimnm('Day',Dimix('Day',!Day)-3) ,
.... and so on and so forth as many times as you think would be necessary to encompass however many blanks are likely to be in a row.


If the Dim1, Dim2 etc all require a different take on this you would possibly need to do it againt a custom control cube rather than the actual attributes cube.


Like I say this is starting to look a very messy method and I would probably try to avoid it myself... but I think it would work.

Of course if you did this the rule from my previous comment would just have a divide by 10 instead of the variable denominator.

[winsonlee]

that looks like a resonable solution that doesnt require much work to maintain it.
but would like to further emphasize that using dimix, the sequence of element created in the dimension is important. eg if Jun is created subsequently all the days follow by Jul and all the days, therefore it will comes to a time where the prior period of Jul-01 will be Jul. So it is advisable to create all parent element first then only the days element so that all the days is in correct orders.

Looks like a good idea. but i would like to confirm about the rules. using the rules given wouldnt it mean May-14 = (May-07 + May-08 + May-09 + May-10 + May-11 + May-14 ) / 6 ?

ah yes I see I mis-read the question.


I now take it as the OP always wants to average 10 days worth of data but it will only ever be the last 10 days that had values in them.

If that is the case I would still have the "Prior" attribute but populate it by a rule (I say this is what I would do... in reality I would use a TI but if a rule was essential).

The rule would be something like:

['Prior Period']=S:
If ( DB(Cube, Dim1, Dim2, Dimnm('Day',Dimix('Day',!Day)-1), 'AVG Job Val')<>0, Dimnm('Day',Dimix('Day',!Day)-1) ,
If ( DB(Cube, Dim1, Dim2, Dimnm('Day',Dimix('Day',!Day)-2), 'AVG Job Val')<>0, Dimnm('Day',Dimix('Day',!Day)-2) ,
If ( DB(Cube, Dim1, Dim2, Dimnm('Day',Dimix('Day',!Day)-3), 'AVG Job Val')<>0, Dimnm('Day',Dimix('Day',!Day)-3) ,
.... and so on and so forth as many times as you think would be necessary to encompass however many blanks are likely to be in a row.


If the Dim1, Dim2 etc all require a different take on this you would possibly need to do it againt a custom control cube rather than the actual attributes cube.


Like I say this is starting to look a very messy method and I would probably try to avoid it myself... but I think it would work.

Of course if you did this the rule from my previous comment would just have a divide by 10 instead of the variable denominator.

[declanr]

that looks like a resonable solution that doesnt require much work to maintain it.
but would like to further emphasize that using dimix, the sequence of element created in the dimension is important. eg if Jun is created subsequently all the days follow by Jul and all the days, therefore it will comes to a time where the prior period of Jul-01 will be Jul. So it is advisable to create all parent element first then only the days element so that all the days is in correct orders.

What I would be more tempted to do in that situation is create an extra "dummy" dim that just contains all the N-Level elements in the correct order... prefix it with a z or just give it a "Don't you dare bloody touch this" style name and have it update say once a year as part of the year end rollover etc.

That way you don't have to worry about people updating the orders to fit other purposes etc since it isn't used for anything else and has no other purpose.

[winsonlee]

haha... thats right. that would resolve all the sequence issue.

[LORR]

Firstly, thank you all for your kind replies, much appreciated.

that looks like a resonable solution that doesnt require much work to maintain it.
but would like to further emphasize that using dimix, the sequence of element created in the dimension is important. eg if Jun is created subsequently all the days follow by Jul and all the days, therefore it will comes to a time where the prior period of Jul-01 will be Jul. So it is advisable to create all parent element first then only the days element so that all the days is in correct orders.

What I would be more tempted to do in that situation is create an extra "dummy" dim that just contains all the N-Level elements in the correct order... prefix it with a z or just give it a "Don't you dare bloody touch this" style name and have it update say once a year as part of the year end rollover etc.

That way you don't have to worry about people updating the orders to fit other purposes etc since it isn't used for anything else and has no other purpose.

I think this is quite a good approach and as suggested TI should be the way to go...
I will implement this in the next couple of days and let you all know how it goes... so once again thank you..