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!
How to write the Rules for the latest non-zero value
-
- 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
- Attachments
-
- Cost Price img.jpg (67.55 KiB) Viewed 2233 times
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: How to write the Rules for the latest non-zero value
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:
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:
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.
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'];
Code: Select all
['Cost Price'] => ['Calc Price'];
Robin Mackenzie