Function to take the last non-zero value from previous months

[jj]

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!

[MichelZ]

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

[jj]

Hi Michel,

I will try this.

Thanks!