COGNOiSe.com - The IBM Cognos Community

Planning & Consolidation => TM1 => Topic started by: jj on 06 Apr 2010 07:15:19 AM

Title: Function to take the last non-zero value from previous months
Post by: jj on 06 Apr 2010 07:15:19 AM
Hi Everyone,

I have a two dimensional cube with a months dimension and a two element dimension as follows:

                 Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
Price            10   10    10    12   14   14
Last Price      10   10    10    12   14   14  14    14    14    14   14    14

The cube contains actual prices from Jan to Jun. Jul to Dec are forecast periods.

What i'm looking for is a TM1 function or formula that can automatically take the last non-zero price from a previous month and extrapolate across forecast months. For instance, Jun contains the last non-zero price of $14. I need a formula to enter in the Last Price element that would take this last non-zero price and push it forward across the forecast months. In Cognos EP, this formula would be the Last BIF. Is there somethning similar in TM1?

Thanks!

Title: Re: Function to take the last non-zero value from previous months
Post by: MichelZ on 06 Apr 2010 07:40:09 AM
Hi,

Based on your described situation you could do something like this:

['Jan','Price'] = N: ['Last Price'];
['Price'] = N: IF(['Last Price'] <> 0, ['Last Price'], DB('CubeName', DimNm('Month', DimIx('Month', !Month)-1), 'Price'));

The second rule relies on the correct dimension index order for the elements in the Month dimension. A safer option could be to define a 'Prev Month' attribute on the Month dimension that holds the name of the previous month. Based on this attribute the second rule could look ike this:

['Price'] = N: IF(['Last Price'] <> 0, ['Last Price'], DB('CubeName', AttrS('Month', !Month, 'Prev Month'), 'Price'));


Michel
Title: Re: Function to take the last non-zero value from previous months
Post by: jj on 06 Apr 2010 07:56:47 AM
Hi Michel,

I will try this.

Thanks!