TM1 Forum

I basically want to find the first non-zero cell in a cube backwards.
e.g. if I want to cal a cell for 2013 which is based on previous years, I want to skip a zero cell for 2012 and get the non-zero base value from 2011.
If it is zero for 2011 also, then get it from 2010 and so on.
I only can use loop to implement this logic but I don't think rule support it. Any idea would be appreciated.

macsir wrote:I basically want to find the first non-zero cell in a cube backwards.
e.g. if I want to cal a cell for 2013 which is based on previous years, I want to skip a zero cell for 2012 and get the non-zero base value from 2011.
If it is zero for 2011 also, then get it from 2010 and so on.
I only can use loop to implement this logic but I don't think rule support it. Any idea would be appreciated.

Rules don't support loops. They're formulas, not programming code.

If the situation is as simple as you describe, nested If() functions would work just as well given that I'd expect there to be a limited number of years that you could go back.

Alternatively if the prior values won't change you could write a TI to go back through the history and populate another scenario called something like "Last_Non_Zero_Value", plucking it from whichever year the value is found in. Obviously in TI you can do loops and would be able to do it in the way that you describe.

Alan Kirk wrote:

macsir wrote:I basically want to find the first non-zero cell in a cube backwards.
e.g. if I want to cal a cell for 2013 which is based on previous years, I want to skip a zero cell for 2012 and get the non-zero base value from 2011.
If it is zero for 2011 also, then get it from 2010 and so on.
I only can use loop to implement this logic but I don't think rule support it. Any idea would be appreciated.

Rules don't support loops. They're formulas, not programming code.

If the situation is as simple as you describe, nested If() functions would work just as well given that I'd expect there to be a limited number of years that you could go back.

Alternatively if the prior values won't change you could write a TI to go back through the history and populate another scenario called something like "Last_Non_Zero_Value", plucking it from whichever year the value is found in. Obviously in TI you can do loops and would be able to do it in the way that you describe.

Thanks, Alan. I agree with you. Now I am updating the rule with limited years.

You can do this if you have two measures.
You need to feed it with a forward attribute though

Duncan P wrote:You can do this if you have two measures.

Code: Select all

['new value entry'] = N: STET;
['effective value'] = N: IF( ['new value entry'], ['new value entry'], DB( 'this cube', ... , ATTRS( 'time', !time, 'previous' ), 'effective value' ) );

You need to feed it with a forward attribute though

Code: Select all

['new value entry'] => ['effective value'];
['effective value'] => DB( 'this cube', ... , ATTRS( 'time', !time, 'next' ), 'effective value' );

Thanks for your idea, I will try to test it.