Rule test if cell blank vs Zero

[damientaylorcreata]

Hi Guys,

Just wondering how do I test whether a cell is blank (or empty/null) as opposed to containing a zero. My issue is as below:

Cube: Stock (Inventory)

My cube rule looks like this:

Code: Select all

['Average Cost Balance'] = If((['Average Cost'] = 0), DB('Stock' , !Item, !Company, If(ATTRS('Period',!Period,'Prior') @= 'Dec', ATTRS('Year',!Year,'Prior'), !Year), ATTRS('Period',!Period,'Prior'), 'Average Cost Balance', 'XXX'), ['Average Cost' ]);

This rule basically loops through each of the cells in my inventory/stock cube and if it finds zero it goes backwards through the months and years until it finds the last available rate. However it has been brought to my attention that it is possible for the last rate to itself be zero, which causes a problem, because my rule is wanting to replace all zero valued cells with the last available value.

Therefore it would be great if I could start the cube off by setting all values to empty instead of zero and then change the rule to match against empty or '' or something like that. Therefore I would only be replacing empty cells and not zero value cells.

Any ideas?

[Steve Rowe]

This shouldn't matter Damien and should fail silently, also since you hopefully are not feeding the combinations where there have never been a value then there should be minimal overhead.

The only way to tell the difference between zero and null is to use undefvals in the rules but it's pretty nasty.....

HTH

[damientaylorcreata]

Hi Steve,

Thanks for you reply.

Yes, from what I have read I don't think I want to go anywhere near undefvals. Therefore it seems that there is no nice way of being able to distinguish between zero's and blanks which makes my task quite difficult. I will have to try and find some other solution in this case.

Basically I want any zero value for a given year and month selection to be replaced with the last average cost price in that same selection, but I don't want to replace zeros that are actually meant to be zeros.

For instance if I have the following average costs for item XXX:

2009, Jan = 0.50
2009, Apr = 0.45
2009, Dec = 0

I would like all periods before Jan 2009 to be zero and all periods between Jan 2009 and Mar 2009 to be 0.50 and all periods between Apr 2009 and Nov 2009 to be 0.45 and then everything from Dec 2009 and onwards to be 0.

My current rules (as show in my original post) would assign the value of the previous period if the currently selected period contains zero and works its way forward, however when it reaches Dec 2009, it assisns 0.45 instead of zero (which is not what I am wanting to happen).

Therefore the only way I can see around this is to in my TI process convert all zero values to some extreme figure such as 10000 and then after the first rule has completed, run another rule that converts all 10000 figures back to zero.

However this doesn't seem very elegant in either design or performance and was hoping there would be a better way of doing this. I would have though this to be a common scenerio.

By the way, hope you have a great New Years Eve!

Thanks,
Damien

[Steve Rowe]

If you need to stop the rule rolling forward into the future it's common to put a current period flag somewhere in the system and then test against it in your rollforward rule.

Cheers and Happy New Year to you!

[damientaylorcreata]

Hi Steve,

I have got it sorted out now.. I simply imported all the zero value figures as -1 into TM1 and then after my rules were processed, I simply converted all occurances of -1 back to zero and works well now.

Thanks for your help.

[Jeroen Eynikel]

Hi Steve,

I have got it sorted out now.. I simply imported all the zero value figures as -1 into TM1 and then after my rules were processed, I simply converted all occurances of -1 back to zero and works well now.

Thanks for your help.

Alternatively you can use a second measure, but of type string. I have done this a few times. Let data entry / load be done on the string measure. Then you can write a rule for the numeric measure performing the check on the string measure. This works well enough - you only need to distinguish between the input (string) measure and the reporting (numeric) measure.