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!
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
Hi Michel,
I will try this.
Thanks!