Rolling Average for Quarters

[amitbehere2002]

Scenario :-
1 When we are calculating we need to go back to first 20 quarters for e.g. from Q1 2013 to last 20 Quarters for Rolling Average
2.while calculation condition must be satisfied of having at least 75% Values not Null out of 20 Quarters
3. if 20 Quarters doesn't have null Value then i will calculate all 20 Quarters for Rolling Average
4. To calculate Rolling Average we must have 15 Values in Place
5. if last 20 quarters doesn't Satisfy the Value of 15 as 75 % not null Values i need to calculate beyond 20 Quarters till i don't receive my 75% Values

Can you Please help me regarding the same

[qml]

Have you read this? Think about it, how are we supposed to provide any help without knowing anything about your model and the things you have done? Your scenario is specific enough, but you have provided absolutely no context.

[amitbehere2002]

Following is the Example for the Rolling Average
1 Q3 2008 27980
2 Q4 2008 10054
3 Q1 2009 53762
4 Q2 2009 49638
5 Q3 2009 69453
6 Q4 2009 72014
7 Q1 2010 23292
8 Q2 2010 87890
9 Q3 2010 47597
10 Q4 2010 98952
11 Q1 2011 74982
12 Q2 2011 28498
13 Q3 2011 96301
14 Q4 2011 35430
15 Q1 2012 91058
16 Q2 2012 36774
17 Q3 2012 52734
18 Q3 2012 34624
19 Q4 2012 16279
20 Q1 2013 74860


Above given are 20 values for Quarters
Where in there is not a single value is null
But in my Scenario
1.while calculation condition must be satisfied of having at least 75% Values not Null out of 20 Quarters
2. if 20 Quarters doesn't have null Value then i will calculate all 20 Quarters for Rolling Average
3. To calculate Rolling Average we must have 15 Values in Place
4. if last 20 quarters doesn't Satisfy the Value of 15 as 75 % not null Values i need to calculate beyond 20 Quarters till i don't receive my 75% Values
Please Suggest me any help for this issue

Thanks
Amit

[jameswebber]

Amit,
Does this help?
http://blog.tm1tutorials.com/2011/08/17 ... ould-know/
Still not fully sure what you are trying to do

[amitbehere2002]

Hi James,

Thanks a lot for your help
but this will not work out for solution which i need for Rolling Average Calculation


Thanks
Amit

[rozef]

Amit,

do you want to make it with a rule or with a process?

Rules would be not an easy task, beacause you will have to go through 20 quarters (or more).

With a process, you can build an MDX subset with filter on cube values and Head function that will give you what you want.

[amitbehere2002]

Hi,

I want to achieve this with the help of Rules
Please guide me for the same

Thanks
Amit

[rozef]

Well first I must tell you I really thinks it is not a good idea to make it with rules because of the complexity of doing it, maintain it, the time and RAM that can consume depending the size of data... Did your client really need to get their rolling instantaneously? If not it is really not a big deal to launch a process onth a month and it can help a lot many features of an TM1 application.

Anyway, here is how I would try to get it with rules.

First thing is to have an attribute on quarters pointing on previous quarter.
Add an indicator called "Rank_Value" in the same dimension of amounts to calculate if there is an amount then add 1 to the previous rank else copy it.
Add a second indicator called "Value_of_Rolling" in the same dimension of amounts that copy the amount if "Rank_Value" > 0 and < 15 and > previous Rank.
The sum of your "Value_of_Rolling" divised by 15 will give your rolling average.

You will have to add some condition to include the 20 quarter case, I just give you the main idea.

[jameswebber]

I'm not sure why but Declanr made the following post on this topic that I got in my inbox but doesn't seem to have made it to the page.
I think this is the solution or as good as you will get without paying for some TM1 consulting.


declanr said:

Amit,

It would help if you could provide some info on your cube structure but this is a pretty simple result to achieve using a TI (it could also be done in a rule but would be a bit messy); this will be very easy for an IBM Certified TM1 Developer with Extensive experience in Cognos Planning and Cognos TM1 to implement I am sure.

Code: Select all

sQuarter = 'Q1 2013';

iCount = 0;
iStop = 0;
iCheck = 0;
nTotal = 0;
While ( iStop <> 1 );
    nVal = CellGetN ( sCub, &#46;&#46;&#46;, &#46;&#46;&#46;&#46;, sQuarter, 'Amount' );
    If ( nVal <> 0 );
         iCheck = iCheck + 1;
         nTotal = nTotal + nVal;
    EndIf;
    iCount = iCount + 1;
    sQuarter = Dimnm ( 'Quarters', Dimix ( 'Quarters', sQuarter ) - 1 );
    If (  ( iCheck = 15 ) & ( iCount > 19 ) );
        iStop = 1;
    EndIf;
End;

nAvg = nTotal \ 20;
CellPutN ( nAvg, sCub, &#46;&#46;&#46;, &#46;&#46;&#46;, &#46;&#46;&#46;&#46;, 'Rolling Average' ); 

The code is rather rough but I am certain that a senior consultant at a respected company is more than capable of adjusting it to suit their needs and reflect the actual logic e.g. is the average always divided by 20 or does how many quarters are populated effect that etc.

HTH your customer.

[declanr]

I'm not sure why but Declanr made the following post on this topic that I got in my inbox but doesn't seem to have made it to the page.
I think this is the solution or as good as you will get without paying for some TM1 consulting.


declanr said:

Amit,

It would help if you could provide some info on your cube structure but this is a pretty simple result to achieve using a TI (it could also be done in a rule but would be a bit messy); this will be very easy for an IBM Certified TM1 Developer with Extensive experience in Cognos Planning and Cognos TM1 to implement I am sure.

Code: Select all

sQuarter = 'Q1 2013';

iCount = 0;
iStop = 0;
iCheck = 0;
nTotal = 0;
While ( iStop <> 1 );
    nVal = CellGetN ( sCub, &#46;&#46;&#46;, &#46;&#46;&#46;&#46;, sQuarter, 'Amount' );
    If ( nVal <> 0 );
         iCheck = iCheck + 1;
         nTotal = nTotal + nVal;
    EndIf;
    iCount = iCount + 1;
    sQuarter = Dimnm ( 'Quarters', Dimix ( 'Quarters', sQuarter ) - 1 );
    If (  ( iCheck = 15 ) & ( iCount > 19 ) );
        iStop = 1;
    EndIf;
End;

nAvg = nTotal \ 20;
CellPutN ( nAvg, sCub, &#46;&#46;&#46;, &#46;&#46;&#46;, &#46;&#46;&#46;&#46;, 'Rolling Average' ); 

The code is rather rough but I am certain that a senior consultant at a respected company is more than capable of adjusting it to suit their needs and reflect the actual logic e.g. is the average always divided by 20 or does how many quarters are populated effect that etc.

HTH your customer.

Yes, I was having a particularly sarcy afternoon (and there is a backstory as to why this post particularly riled me) and wrote this out.

I quickly then deleted it however as I myself get annoyed with some of the petty and insulting comments that get posted on here by experienced professionals (who really should be more... well... professional) and then realised I just done the same.

However since the comment is now back out in the world; I will point out that the code is for TI and I agree entirely with Rozef in the sense that you shouldn't use a rule for this sort of thing unless absolutely necessary (and nothing is absolute.)

Also the "&#46;" was originally a "." so:

Code: Select all

CellGetN ( sCub, ..., ..., ..., sQuarter, 'Amount' ); 

Must have got distorted somewhere along the email highway.

[jameswebber]

Opps sorry didn't realise that declanr, I wouldn't have posted it in that case. I just thought it might be related to the previous forum issues.

Your not too aggressive in your answer I would just change

The code is rather rough but I am certain that a senior consultant at a respected company is more than capable of adjusting it to suit their needs

to

This should be enough steer for you to figure out the rest, good luck

[declanr]

No worries James, I am a firm believer that we lose all rights to anything we post in a public domain and I should have thought before clicking send!