Calculate from day before

[Willi]

Hello,

I have a cube with 4 Dimensions:

- Year
- MonthDay
- People
- Measure

The structure of Year is:

Code: Select all

Alle Jahre
2011
2012
2013
2014
2015
.
.
.

The structure of MonthDay is:

Code: Select all

Alle Monate
  01
    0101
    0102
    0103
    0104
    0105
    0106
.
.
. (until 1231)

People is like Year just with names and Mesures are some Measure-Numbers.

Now I want to calculate one Measure of one day from another Measuere the day before. I tried the following rule:

Code: Select all

# To leave the first day untouched
['2011','0101','Bestand'] = N: STET;
# Calculation "Bestand from Today = Kasse from Yesterday"
['Bestand'] = N: DB('Cube',
                    SUBST(DATE(DAYNO(!Year|'-'|SUBST(!MonthDay,1,2)|'-'|SUBST(!MonthDay,3,2))-1,1),1,4),
                    SUBST(DATE(DAYNO(!Year|'-'|SUBST(!MonthDay,1,2)|'-'|SUBST(!MonthDay,3,2))-1,1),6,2)|SUBST(DATE(DAYNO(!Year|'-'|SUBST(!MonthDay,1,2)|'-'|SUBST(!MonthDay,3,2))-1,1),9,2),
                    !People,
                    'Kasse');

I guess the calculation of "Yesterday" could be a bit easier but it workd. Thst's not my Problem.

If I start the Server and start Cubeviewer with this year I see "#N/A" and the "Circular reference" Error Message for the Measure "Betrag". But when I Change the year to 2011 it's working. and when I step in cubeviewer from year to year until today I see the correct value for today. Also when I Change a value in the back years I get the Error for today until I step through the cubeviewer year by year

Is my Approach possible and how can I get the correct values at once?

Thx and regards,
Willi

P.S: It's TM1 Version 10.2.2 FP1

[tomok]

If you are getting a circular error message for "Betrag" then I can assume it is a rule calculated measure as well. Why did you not include the code for that calculation? Don't see how anyone can help you without it. As for your method of figuring out the previous day, while it may work I would avoid it. String calcs are some of the slowest. You'll always know what the previous day value will be for a specific day AND it will never change. Better to create an attribute called "PreviousDay" and populate it with the element reference for that day. The you can replace your SUBST mess with ATTRS('MonthDay', !MonthDay, 'PreviousDay').

[Willi]

Thx for the reply. I'm just in the process to Change the SUBST-mess with an Attribute.

But the other Problem: I totally agree that I should have a look at the calculations if there won't the correct calculation after I stepped year by year through the cubeviewer!?

[tomok]

But the other Problem: I totally agree that I should have a look at the calculations if there won't the correct calculation after I stepped year by year through the cubeviewer!?

I have no clue what you are trying to say/ask with that statement.

[Willi]

Sorry. I attach some Little Screenshots. I start the Server and the Cubeviewer and see this:

Rule1.JPG (22.31 KiB) Viewed 5897 times

Than I only Change the upper left selection to the first year and get the result in Picture Rule2.JPG

Rule2.JPG (22.67 KiB) Viewed 5897 times

Than again I Change the year to the second year: (Attachement deleted, probably only 3 allowed)

and so on until I'm back to the first selection:

Rule4.JPG (25.99 KiB) Viewed 5897 times

That's what i don't understand and what makes me think that it's not a circular refence-issue.

[kangkc]

Check the server log, I suspect you may be hitting stack overflow issue with rule. This is not a TM1 bug but rather some application re-design is required.
The error may appear as circular reference when you trace the rule.

Stack overflow is common if there is a reference back in rule which exhausted the stack buffer. Till date I have not been able to determine the actual stack buffer allocated in TM1 but I think it's different for respective release of TM1.

[Willi]

Thx, that was really the Problem.So for now I put this in a TI and rethink later if I can Change it to a rule in another way.

[qml]

I think the size of the rule stack and therefore the achievable level of recursion is still in the vicinity of 255 (you can definitely do a search of the forum, it has been discussed).
If you break it down in such a way that the first day of each quarter or month is STETed out then you only need to use TI to populate that first day of each period and the rest should be ok to do using rules. Of course a lot depends on whether you are still getting acceptable performance with that level of recursion.

[tomok]

I use this "previous period" concept all the time in TM1 and never have any issues with stack overflows. You must be daisy-chaining these "yesterday" values past the allowable stack limit. If "yesterday" is always based on "today" from a day ago and "today" is not dependent on the "yesterday" value then you should never have a stack overflow. If you are doing some daisy-chaining then you should rethink your logic.