How to write the Rules for the latest non-zero value

Post Reply
kiss351523
Posts: 51
Joined: Thu May 05, 2011 2:05 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007 and 2003
Location: Asia

How to write the Rules for the latest non-zero value

Post by kiss351523 »

Dear all,

I have a Cube of Cost Price by month, some of months, the cost price is zero, but i need the latesst non-zero price for the calculation, just like the ATTACHMENTS.

How to write the Rules for the Calc Price?

ths!
Attachments
Cost Price img.jpg
Cost Price img.jpg (67.55 KiB) Viewed 2233 times
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: How to write the Rules for the latest non-zero value

Post by rmackenzie »

You need an attribute on the Month dimension that gives the prior month (e.g. Jan is the prior month to Feb) and also a consolidation for all the months which I called 'Total' in the example below. The code might look like this:

Code: Select all

SKIPCHECK;

['Calc Price', 'Jan'] = N: ['Cost Price', 'Jan'];
['Calc Price'] = N: IF ( DB ( 'Cube', 'Cost Price', !Month ) = 0,
  DB ( 'Cube', 'Calc Price', ATTRS ( 'Month', !Month, 'Prior Month' ) ),
  ['Cost Price'] );

FEEDERS;

['Cost Price'] => ['Calc Price', 'Total'];
Note the over-feeding. In a real cube, this isn't optimal, but shows how to feed in a very basic example. An alternative feeding strategy is to always load the current price to remaining periods so there is always a value in Cost Price (rather than just entering by exception per your example). Using this strategy the feeder can be simplified to:

Code: Select all

['Cost Price'] => ['Calc Price'];
Writing a TI to do this 'seeding' across a number of other dimensions (profit centre, product, region etc) may be a pain, but it may well be less painful than a performance obstacle caused by some lazy coding doing an over-feed like I've done above.
Robin Mackenzie
Post Reply