Obtain last non zero value in a date range

[damientaylorcreata]

Ok.. I have followed your instructions and it seems that no matter what day I select or what value appears, I do not get the option "Trace Feeders" as you can see from the attached screenshots.

I have attached a copy of the view that I am referring to. You can also see that when I right click on a cell I do not get the Trace Feeders option. And when I try and select "Check Feeders" I receive a message as displayed in the screenshot.

"You have selected a consolidation cell containing 5.4e+012 simple cells, which exceeds the maximum of 3.e+006 allowed on the server....."

I have also provided you a screenshot of what displays when I right hand click "Trace Calculation" on Day "03".

In regards to C vs N levels. Basically from what I understand my view is allowing users to select a consolidation for elements such as Warehouse, Project, Partner, etc. However I imagine for the rule to work it would need to be based on the N level that such as Project 000000 and Partner 0000, etc. As all costs are stored against these elements. And from what I understand EV requires that the rules all be N level based.

Thanks. Damien

[damientaylorcreata]

I forgot one screenshot as attached.

Thanks,
Damien

[Steve Rowe]

Hi damien,
The reason that you don't get trace feeder is that you are looking at the C level.
A golden rule for any kind of development of rules and feeders is to only look at the N level, the C level should take care of itself as it's just a sum (usually).
You can only really test the behaviour of your system so at the N level of the cube.
Can you repeat but with all the cube references at the N level.
Cheers

[damientaylorcreata]

Hi Steve,

The screenshots provided were with N: in the rules, the exact same rule that you provided me with. I have checked the other rules in the cube and they also have N:.

Thanks,
Damien

[Steve Rowe]

But the cube view you are looking at is C level, we can only see what is going on in an N level view

[lotsaram]

Try "Check Feeders" as opposed to "Trace Feeders".

Check Feeders is for the calculated measure that is being fed and will tell you if any cells are not fed (blank result = all OK). This can be done at C level.
Trace Feeders traces from the value doing the feeding and shows you where the feeder is going to.

[damientaylorcreata]

Please find attached the screenshots of the Trace Feeders for Days 01, 02, 03 at the N level. Hope this helps. Thanks, Damien

[Steve Rowe]

Hi damien

We've managed to drop a feeder somehow, we need this one as well

['Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType);

The above feeder is the main feeder for the system and the one that would break the feeder stack. The one with the date in are the extra ones we put in to get past the break points in the attribute.

Hopefully this will get Jan working and then we can look at extending for all periods.

Cheers,

[damientaylorcreata]

Steve.. You are a legend! I have added this extra feeder and it now works for all months in both Perspectives and EV!! I am so happy! Thanks for all your help. I am now going to experiment going between periods by adding 31 into the prior for Day 01 and add 01 into next attribute for Day 31 and modify the rule to use a next and prior. The rule below worked well in Perspectives.. But when I modified the feeders as below and when attaching the new rules it just started chewing through the memory and I know that there are people currently using TM1, so I killed the saving of the rules and will try it again early tomorrow morning. However I thought that was the whole purpose of using rules as apposed to storing the redundant data. Anyway I am very happy with the progress.. Will let you know how I go.

Code: Select all

['Average Cost (LTD)'] = N:
If ( ['Average Cost' ] = 0,
DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Prior') @= '31',  ATTRS('Month',!Month,'Prior'), !Period), ATTRS('Day',!Day,'Prior'), !Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType) ,
['Average Cost' ] );

Feeders;

['Average Cost' ] =>['Average Cost (LTD)'] ;
#Feed tomorrows applied from today?s applied,
['Jan','Day':'01','Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Next') @= '01',  ATTRS('Month',!Month,'Next'), !Month), ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Part
ner, !OrderType);
['Jun','Day':'01','Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Next') @= '01',  ATTRS('Month',!Month,'Next'), !Month), ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Part
ner, !OrderType);

['Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Next') @= '01',  ATTRS('Month',!Month,'Next'), !Month), ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType);

Thanks,
Damien

[Steve Rowe]

Hi Damien,
I expect EV needs cells to be fed before it displays correctly, not too much of a surprise really.

Now that you have extended you feeders to deal with months you are feeding a much bigger data set than before so the rule save will take much longer. The firing of the feeders (think flags in cells) is what takes the time when saving a rule.

Don't forget you need to deal with the year in a similar way that you have dealt with month.

One thing I would do that will make a small difference to the speed is to change the If test
If(ATTRS('Day',!Day,'Prior') @= '31', ATTRS('Month',!Month,'Prior'), !Period)
to
If(!Day@= '01', ATTRS('Month',!Month,'Prior'), !Period)

same (but the opposite) on the feeder side.
If(ATTRS('Day',!Day,'Next') @= '01', ATTRS('Month',!Month,'Next'), !Month)
to
If(!Day @= '31', ATTRS('Month',!Month,'Next'), !Month)
assuming this does not mess up anything for 30 or 28 day months.

Cheers

[damientaylorcreata]

Yes, Thanks Steve.. I have made these changes and it works perfectly! Even though everything is working well could you please confirm that I understand what is going on:

When rules and feeders are saved in the rules editor, the relevant placeholders are being created into memory and then when a tm1 view is executed it uses these feeders to process the relevant rules?

In regards to how these recursive rules / feeders work could you please confirm that my understanding is correct for the following rule:

Basically the rule below is executed for each Average Cost (LTD) cell from the current view. If the initial value of Average Cost is zero, it gets the prior day from the prior attribute and if this is zero it will store zero, but I don't understand why it doesn't stop there, but just keeps looping to the next one.

Code: Select all

['Average Cost (LTD)'] = N:
If ( ['Average Cost' ] = 0,
DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Prior') @= '31',  ATTRS('Month',!Month,'Prior'), !Period), ATTRS('Day',!Day,'Prior'), !Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType) ,
['Average Cost' ] );

In regards to the feeders, I understand that the first feeder flags each of the Average Cost (LTD)'s where there is an Average Cost. Then the next feeder simply gives a starting point (or as you put it a break point) whereby we have a definate value that we can start from.

Code: Select all

['Jan','Day':'01','Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Next') @= '01',  ATTRS('Month',!Month,'Next'), !Month), ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Part
ner, !OrderType);

From what I understand this will just feed one cell (which in this case would be Day 02 Jan), but then the following feeder does the rest and recursively flags every next cell all the way till the end of the year.

Code: Select all

['Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, If(ATTRS('Day',!Day,'Next') @= '01',  ATTRS('Month',!Month,'Next'), !Month), ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType);

Would you be able to confirm that based on the above, that I fully understand what I have done with these rules/feeders and if not help me fill the gaps.

I just can't find any good documentation on this topic. The best I can find is the Rules documentation (http://download.boulder.ibm.com/ibmdl/p ... nglish.pdf), but it doesn't go into this kind of depth.

Thanks so much for all your help!

[Steve Rowe]

Hi damien, I think you have pretty good handle on what is going on.

With regard to the recursion rule it does not really loop back, say we look at Day 05, Average Cost (LTD) and nothing has been evaluated yet and we have a value in average cost in Day 01. The rules engine does something like this.

1.Day 05 = Day 04 since Average =0, Day 04 has not been calculated yet so I need to calculate that
2.Day 04 = Day 03 since Average =0, Day 03 has not been calculated yet so I need to calculate that
3.Day 03 = Day 02 since Average =0, Day 02 has not been calculated yet so I need to calculate that
4.Day 02 = Day 01 since Average =0, Day 01 has not been calculated yet so I need to calculate that
5.Day 01 = Average since Average = 19.99 (say).
6. Now I know what Day 01 is I can say what Day 02 is 19.99
7. Now I know what Day 02 is I can say what Day 03 is 19.99
8. Now I know what Day 03 is I can say what Day 04 is 19.99
9. Now I know what Day 04 is I can say what Day 05 is 19.99

In "English" the recurisve bit of the rule is today=yesterday so even when we look at Day 05 the engine is obliged to roll back through all the yesterdays to deliver a result. Not sure if that makes it clearer or not!

[damientaylorcreata]

yes, I get it now.. Crystal clear!

And therefore if any of those cells is not fed it will stop, for example if day 03 is not being fed, it will not be calculated and therefore no value will be returned and it will go no further and therefore the value for Day 05 will end up with zero.

Thankyou so much for your help.. I have learned so much about feeders and rules!