Page 1 of 1
How to write the Rules for the latest non-zero value
Posted: Wed Nov 19, 2014 7:30 am
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!
Re: How to write the Rules for the latest non-zero value
Posted: Wed Nov 19, 2014 8:05 am
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:
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.