stack overflow(please help!)

[macsir]

Hi, guys

I know usually when we have long feeder chain, it is possible to have this stack overflow issue. My current problem is we forecast next 3-4 years at day level and using rolling average way for forecasting. E.g. to forecast day1, we need to go back 60 days actual to do average. And day2 is using day1 forecast plus last 59 days actual's average. And so on so forth until next 4 years. We usually have around 1000 - 1300 days to go forward with average forecasting.

In the date dim, I create my own date key to move backwards or forwards. Rather than using next, previous attributes, it will simply use day +/- n to move around in the rule. The code I am using is like that,

Forecast dayN avg = Actual or Forecast (dayN-1 + dayN-2+ ... + dayN-60) / 60;
Feeders;
Actual or Forecast avg=> Forecast dayN+1,
Forecast dayN+2,
....,
Forecast dayN+60;

Although the rule is long, but still logical. However, whatever I tried, it always hitting this stack overflow issue as well as cube view is very slow when there are many forecast days to show because the feeder chain is too long and deep. I know we have to break it to make it work and fast. However, I can't think of how to break it. Is that when I create this date key, I make every half year blank? So that the feeder can't continue due to blank for example , at day180, the key is blank. And then what? How to bridge the gap? Because whenever the feeder's calculation comes to day180, it will point to blank. At this stage, I know need to something like
Forecast avg day179=> Forecast ATTRS(dim, day179, 'Next');
But in this formula's left side, how do I know that forecast avg is at day179? We can't use any IF or conditions to locate day179's value. The right side is fine. Do I need to create some hierarchies in the date dim to have specific relative date based on current forecast date under day179 parent so that I can use it in the rule?

Please give me advice if any of these thought is wrong. I am really stuck in this implementation.

[macsir]

And if leave day180's key blank. The forecast avg around day180 won't be right as day180 is missing.

[lotsaram]

To use good Australian vernacular, your design is up sh*t creek.

I'm betting at short odds that you have a single date dimension at daily grain. In which case CONSOLIDATIONS! Build hierarchies for each day of 60 days back, forwards whatever. The element weightings can even be 1/60 so that the average calculation is completely automated.

This will clean up your rule and make calculation about 100 times faster.

Fix this first, then let's worry about the feeding.

[macsir]

Hi, lotsaram

Yes, you are right! I am too focusing on how to improve calculation speed of average algorithm to forget the biggest advantage of fast calculation in TM1! CONSOLIDATIONS!!!
Thanks for the guide and shed the lights! I will redesign it to see how it comes!

[macsir]

I have implemented this consolidation in the dim and rule. Really faster but just one minor displaying issue. In order to get day2 forecast average right, I have to use the rule to allow forecast to get value from day-1 to day-59's actual. This make the cube view not continuous to the user when they bring the version and date together into row. The duplication appears in the middle across actual and forecast. Is there any elegant way to suppress the first chunk of forecast average, which is actual in the view? If not, I will just train the user to use forecast to build the view. Thanks.

[macsir]

I think I have to create another measure to copy it from this calculation measure to make is continuous in this measure.
But is won't work if user wants to overwrite any forecast average. It has to be on the original measure.

[macsir]

I figured out that I can create a consolidation with all forecast days in it, and use 60 days' actual to feed them. Then the moving average won't have any stack overflow issue. Special thanks to lotsaram to make me think about this deeply and seriously!

[lotsaram]

I figured out that I can create a consolidation with all forecast days in it, and use 60 days' actual to feed them. Then the moving average won't have any stack overflow issue. Special thanks to lotsaram to make me think about this deeply and seriously!

Glad to have been of service.
And even more glad to have found praise considering I said your initial design was up sh*t creek!
Not everyone is able to read between the lines to see the tongue in the cheek. Credit to you for rethinking the approach and finding a better way to do it.