Obtain last non zero value in a date range

damientaylorcreata · Post by **damientaylorcreata** » Fri Jul 03, 2009 4:25 am

Hi Guys,

I have an interesting question for which I am not sure if there is a solution:

I have an Inventory cube for which contains SOH hand balances (QTY / Units), however I have been asked to represented the SOH as a dollar value. In which case I would need to multiply the Stock on Hand balance for a particular date with the last available Average Cost for the relevant item.

Please find below 5 of the dimensions that exist in the Inventory cube. There are actually more than 5 dimensions in this cube, however they are not relevant to this problem:

-Item
-Year
-Month
-Day
-Measure (e.g. Stock On Hand (Qty), Average Cost, Stock Movement, etc)

The Stock Movements and Stock On Hand (Qty) dimensions are working fine now (with the help of some rules).

However in order to calculate the Stock ON Hand $ value, I need to be able to multiply the stock on hand qty by the average cost as at the date selected in TM1 (e.g. combination of Item, Year, Month and Day). However the Average cost of an item only changes when sales are made and therefore there isn't a different Average cost per day, but there will only be records for the days that sales are made.

Therefore is there a way that I can import for example 5 Average Cost records from the costing DB table such as below:

Item Date Average Cost
XXXX111 20090630 0.90
XXXX111 20090420 0.77
XXXX111 20090103 0.95
XXXX111 20090102 0.60
XXXX111 20090101 0.66

And then in TM1 If somebody selects Year=2009, Month=04 LTD, Day=25 LTD, TM1 will get the last Day, Month combination containing a non zero value?

I have been thinking hard about how I create a rule to achieve this, but I am starting to feel this is not possible. Not sure if anybody else has attempted this kind of problem?

Thanks!

lotsaram · Post by **lotsaram** » Fri Jul 03, 2009 4:47 am

Grasshopper, there is always a way.

There are other ways to do this, but to keep rules clean and simple the easiest way I can think of is to create a dense lookup cube with dimensions year-month-day-cost measure to hold the latest average cost at daily level.

From your Item COS table just push the average cost forwards in your load TI far enough that you are confident there will be another sale of the item before the end of the "push forwards" date range. Once set up this will look after itself pretty easily but to load all daily prices back to when you might conceivably want to start the analysis from could be a bit of a pain.

This structure will mean your rule for Stock Value will be very simple, or you could even populate the measure via TI at leaf level.

The cube will be dense though so you will need enough memory buffer to ensure this isn't a problem.

damientaylorcreata · Post by **damientaylorcreata** » Sat Jul 04, 2009 6:39 am

Thanks for that, but I was hopping to do it without having to import data for every year, month, day combination (and save some RAM) and was hoping there would be a way to handle it with rules instead. But if there are no other ways, I will just have to create the dense cube as you suggested.

damientaylorcreata · Post by **damientaylorcreata** » Sun Jul 05, 2009 7:46 am

Actaully I am wondering if it is possible to do a while loop in TM1 Rules? I know that it can be done in TI, but what about With Rules? If so, I could simply loop over each day until a non zero value is found and then use that value. This would eliminate the need for unnecessary replication of data which would free up memory. The reason being that we are reaching the RAM limited on a machine with TM1.

Therefore if somebody chooses 22 for day, if the location of 22 contains a zero value, it will loop down to 21 and downwards until a non zero value is found and then use that value.

damientaylorcreata · Post by **damientaylorcreata** » Sun Jul 05, 2009 10:49 am

Ok.. I have come up with a solution that I am not 100% satisfied with, but due to the fact that I can't seem to use loops in the Rules editor, its the best I can come up with. Basically I am importing the Average costs for the days they change, plus I am also updating the 1st day of each month with the last occuring cost for the prior month and then using the following rule to run backwards through the days from day choosen backwards till it reaches a day with a cost.

Code: Select all

['Average Cost (LTD)'] =
if(DB('Inventory' , !Item, !Company, !Year, !Month,  ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)),'NLevelDay'), !Project, !TransactionType, 'Average Cost', !Warehouse, !Partner, !OrderType) > 0, 
	DB('Inventory' , !Item, !Company, !Year, !Month,  ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)),'NLevelDay'), !Project, !TransactionType, 'Average Cost', !Warehouse, !Partner, !OrderType),
CONTINUE
);

['Average Cost (LTD)'] =
if(DB('Inventory' , !Item, !Company, !Year, !Month,  ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)-1),'NLevelDay'), !Project, !TransactionType, 'Average Cost', !Warehouse, !Partner, !OrderType) > 0, 
	DB('Inventory' , !Item, !Company, !Year, !Month,  ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)-1),'NLevelDay'), !Project, !TransactionType, 'Average Cost', !Warehouse, !Partner, !OrderType),
CONTINUE
);

['Average Cost (LTD)'] =
if(DB('Inventory' , !Item, !Company, !Year, !Period,  ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)-2),'NLevelDay'), !Project, !TransactionType, 'Average Cost', !Warehouse, !Partner, !OrderType) > 0, 
	DB('Inventory' , !Item, !Company, !Year, !Month,  ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)-2),'NLevelDay'), !Project, !TransactionType, 'Average Cost', !Warehouse, !Partner, !OrderType),
CONTINUE
);

etc

This at least saves storing a value for every day, month, year combination which saves some memory.

If anybody can see a better way of doing this, please let me know. Thanks

mattgoff · Post by **mattgoff** » Sun Jul 05, 2009 1:18 pm

damientaylorcreata wrote:Actaully I am wondering if it is possible to do a while loop in TM1 Rules? I know that it can be done in TI, but what about With Rules?

Nope. While TI is built to use an imperative paradigm, rules are declarative. Loops cannot exist in the latter. To use Excel as an analogy, rules are like cell formulae and TI is like a macro.

Matt

damientaylorcreata · Post by **damientaylorcreata** » Tue Jul 07, 2009 9:39 am

Thanks Matt, thanks for clearing that up. Excellent analogy for explaining the difference between Rules and TI! Thanks

Steve Rowe · Post by **Steve Rowe** » Tue Jul 07, 2009 10:07 am

Damien,
What you need to do with your loop is a typical rollfoward rule.

What you need to do is create two attributes on the day dimension Next and Prior and populate them accordingly, but leave the 1st of Jan and the 1st of June empty in the next attribute.

You rule is then
#If todays average cost is 0 then take yesterdays roll forward value
['Average Cost (LTD)'] =
If ( ['Average Cost' ] = 0,
#Yesterdays rollforward
DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)),'Prior'), !Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType) ,
#Todays input value
['Average Cost' ] );

#The feeder is harder than it should be as if you try and feed forward infinitly then you will blow the feeder stack.
['Average Cost' ] =>['Average Cost (LTD)'] ;
['1st - Jan','Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',DIMNM('Day',DIMIX('Day',!Day)),'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType);

I hope this gives you some idea of where to head with your rules, there was a recent thread on the feeder stack which you should read too. Also it looks like you are trying to do this at the consolidated level of your cube. I'd be very nervous about trying to do this, I'd think very hard about trying to see how I can change my design so that I only have to do the rollforward of the average at the N level.

HTH

lotsaram · Post by **lotsaram** » Tue Jul 07, 2009 10:44 am

Steve Rowe wrote:What you need to do is create two attributes on the day dimension Next and Prior and populate them accordingly ...

Except that it won't be quite that simple as the time dimensions are split into year, month and day so the prior and next day values would need to be held as values in a 4 dimensional time lookup cube (year, month, day, and "day measure").

Having a deep recursive calculation can also be pretty heavy on memory and resources (which is why Steve has advised to put the breakpoints into the attributes. Personally I would also STET these specific days out in the rule.)

Like I said, there is always more than one way, that's part of what makes TM1 such a great business tool.

Steve Rowe · Post by **Steve Rowe** » Tue Jul 07, 2009 11:40 am

True I had not noticed that it was 3d time system.

As ever there is a balance between a dynamic system and the fastest system.

This kind of recursive rule is I think the closest that you can get to a "loop" in rules and is I think a more concise way of writing the rules he has already written.

I think if I was trying to do this I would be storing my average values in a distinct cube without time, that just contains the latest average values. Populate this with a TI during the data load and reference the average price cube with the rules from the main cube. Like your inital suggestion but without the time structure. From what I can see for a given product there is only ever one valid average so time is not required in the lookup cube. This should simplify things somewhat??

Anyway....!

lotsaram · Post by **lotsaram** » Tue Jul 07, 2009 12:15 pm

I like the last suggestion it would be a much better way to go but it all depends what the requirements are. If a stock value needs to be calculated that was correct as of the balance date (as opposed to a revaluation based on current COS) then I can't see an alternative to storing price over time

damientaylorcreata · Post by **damientaylorcreata** » Thu Jul 09, 2009 11:42 pm

You Guys are great! I give this a shot and see how I go.. I will let you know when I have got it to work.
Thanks

damientaylorcreata · Post by **damientaylorcreata** » Sun Jul 12, 2009 8:41 am

Ok.. I gave it a shot and added a Prior and Next attribute to my Day dimension and then added the following rules to my INventory cube:

Code: Select all

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

Feeders;

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

However I couldn't get this to work correctly as follows:

If I focus on the item as follows:

Item Date Average Cost
XXXX111 20090630 0.90
XXXX111 20090420 0.77
XXXX111 20090103 0.95
XXXX111 20090102 0.60
XXXX111 20090101 0.66

If I select Year=2009 and Month=01 and Day=05, It simply returns #n/a, when it really should be return 0.95.

However if select 2009,01,03 first, it will display 0.95 and then if I select 2009,01,04, it still returns 0.95 and then if I select 2009,01,05, it still returns 0.95, all the way up till 2009,01,30 and then I can go back to other figure that was already previously selected and I still get 0.95.

Its almost like the cells are not being fed until I select them for the first time and then I can go back to the cell at anyt time later and the correct value will be returned.

Please note that I have added N: to the rule to specify N level elements are suggested.

Any ideas, on what I may be doing wrong with this rule and feeder? I will keep playing with it in the mean time.

I should also add that yes, the average costs are time specific, for the purposes of providing the stock on hand values at any particular poing in time.

Steve Rowe · Post by **Steve Rowe** » Mon Jul 13, 2009 8:35 am

Hi damien,

The dimix and dimnm in both your Attrs refrences looks redundant, I think this should just be !day. Attrs ('Day' ,!Day, 'Prior or Next')

The #n/a may be coming from the feeder stack issue mentioned, check you message log. This may clear by removing the 1st of jan reference at the begining fo the roll forward feeders.

HTH

damientaylorcreata · Post by **damientaylorcreata** » Mon Jul 13, 2009 10:50 am

I found out what was missing:

ReevaluateConditionalFeeders=T

Once I added this to my tm1s.cfg file, it works perfectly in perspectives for every year, month and day combination.. So happy! However, now I have to work out why the numbers are not showing up in EV.. The figures only show up when I am in Data Entry mode.. However I have a feeling this is due to the rule being at the C level.

Thanks guys for all your help.

Steve Rowe · Post by **Steve Rowe** » Mon Jul 13, 2009 11:14 am

Hmmm
I'm not sure that this would be related to your issue. Certainly the feeder you show in your post is not conditional and having that parameter set to true can have big performance impact on your system.

It may be that the restart you did to add the parameter to the config file is what cleared the problem you were having. When the feeder stack is broken I have seen different behavour on server start-up to rule saves, you should check if your system carries on working of you recompile the rules and that there are no messages relating to feeder stacks in the message log.

I'm pretty sure that you will be breaking the feeder stack if your next attribute does not have a break point. I'm guessing you've not done this as you would need an extra feeder for Jun Day 01.

Can't help pn the EV side!

Note also that there is a small logic hole in your feeders that requires an average prive for the 1st of Jan every year.

damientaylorcreata · Post by **damientaylorcreata** » Mon Jul 13, 2009 11:47 am

I don't think I fully understand how feeders work.. I understand the basic concepts as documented in the Rules documentation. However the feed as provided by yourself in one of you previous posts is a bit over my head.. I have removed the "ReevaluateConditionalFeeders=T" and it is back to erroring, so for some reason this flag allows for this recursive rule to work.

I have added the feeder []=>['Average Cost (LTD)']; For which I assume should feed every cell. As I said this works perfectly well in Perspectives. But not EV... So I will need to play more with it more.

Thanks.

Steve Rowe · Post by **Steve Rowe** » Mon Jul 13, 2009 12:40 pm

This thread discusses the feeder stack.

http://forums.olapforums.com/viewtopic. ... tack#p6527

I'd take the []=>['Average Cost (LTD)']; feeders out as while it may make it work it probably will not scale very well as you over feeding massively. I've just read back through the thread and can see that the Dimix / Dimnm thing we have going on in the rules and feeders is just me copying your original logic and is not required.

The rule and feeder should be
['Average Cost (LTD)'] = N:
If ( ['Average Cost' ] = 0,
DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',!Day,'Prior'), !Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType) ,
['Average Cost' ] );

#Feed todayâ€™s applied value (if the input is non-zero)
['Average Cost' ] =>['Average Cost (LTD)'] ;
#Feed tomorrows applied from todayâ€™s applied,
['Jan','Day':'01','Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',!day,'Next'),!Project, !TransactionType, 'Average Cost (LTD)', !Warehouse, !Partner, !OrderType);

â€¦â€¦â€¦â€¦â€¦..

Ok, I looked a bit harder and I think the rule is probably circular if you have populated the prior attribute for Day 01. This would make Day 01 Jan looks at Day 31 Jan for its value. Day 31 Jan though is dependant on Day 01 for itâ€™s value.

The same problem exists in your feeder, with Jan 31st feeding Jan 01.

This assumes that in your day dimension attributes you have populated Prior Day 01 with 31 and Next Day 31 with 01, is that the case?

If so remove them and restart your system (hope you have a dev environment).

You should now find that January works correctly but no other period does. Let me know how you get on and then we can look at extending the logic for all periods and years.

HTH

damientaylorcreata · Post by **damientaylorcreata** » Tue Jul 14, 2009 10:14 am

Once again thanks Steve.. The link to that thread made it clear to me why you were wanting me to do 2 feeders (one for Jan and one for Jun).

Yeah, I used []=>['Average Cost (LTD)']; so that I could rule out the feeder as being the problem. As this would ensure that every cell would be populated.

I have updated my rules to as what you posted in your last message. I have also ensured that the Prior attribute for Day 01 is blank and that the Next attribute for Day 31 is blank. And yes they were already set to this.

After restarting TM1 and reloading the Inventory cube, I select Year=2009, Month=Jan, Day=01 and as expected both the Average Cost and Average Cost (LTD) contained correct figure and Year=2009, Month=Jan, Day=02 had an Average Cost = 0 and Average Cost (LTD) was equal to the previous cell (and Average Cost=0) (which was also expected).

However when I select Day=03, I then get 0. This suggests that this rule when used with N:, populates Average Cost (LTD) for when Average Cost is populated and will also populate the next day with the previous day's figure, but won't go any further than that.

If I simply do not specify N: in the rule, it will work perfectly in perspectives. But I believe that EV requires that I use all N level rules. Therefore it would be nice if it worked at the N level. I even tried hardcode the N level elements into the rule and feeders and still no luck.

['Average Cost (LTD)'] = N:
If ( ['Average Cost' ] = 0,
DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',!Day,'Prior'), '000000', 'xxx', 'Average Cost (LTD)', 'xxx', '000', 'xxx') ,
['Average Cost' ] );

['Average Cost' ] =>['Average Cost (LTD)'] ;
#Feed tomorrows applied from todayâ€™s applied,
['Jan','Day':'01','Average Cost (LTD)' ]=>DB('Inventory' , !Item, !Company, !Year, !Month, ATTRS('Day',!day,'Next'),'000000', 'xxx', 'Average Cost (LTD)', 'xxx', '00000', 'xxx');

The other dimensions will always be N level anyway, such as item, company, year, month and day.

Just wanted to say thankyou so much for helping me out with this. I am learning so much from this exercise, regardless of whether we find a solution or not.

Steve Rowe · Post by **Steve Rowe** » Tue Jul 14, 2009 10:52 am

Hi damien,
Sounds like we are getting there

In the cube viewer can you right click on the 0 for Day 3 and select Trace Feeders, if you get not fed we will know that the feeders are not working.
Same cell select Trace calculation and post a screen shot of the result.

Can you select Day 01 Average cost, right click and select trace feeders, post the screen shot
Can you select Day 01 Average cost (YTD), right click and select trace feeders, post the screen shot
Can you select Day 02 Average cost (YTD), right click and select trace feeders, post the screen shot

I think the problem may be coming from the some confusion on my part on the N and C level in your system, what if any part of the system that we are looking at is C level?

Cheers

TM1 Forum

Obtain last non zero value in a date range

Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range

Re: Obtain last non zero value in a date range