Creating a rule to adjust calculated values

[mmckimson]

While I know TM1 has a number of ways to spread data, I have a request to recreate some EP functionality that is used for planning expenses. Here's the scenario the end user would like to achieve:

In Cube 1, the end user reviews data from the prior year, enters a planned amount for an entire year and chooses a method to spread the data.

In Cube 2, the data is spread based upon the chosen method.

Cube 2 data then moves to Cube 3, where the cube contains an additional dimension (Final = Adjustment + Preliminary) with the rules for this cube placing all of the spread data in the Preliminary element. The end user can then, if desired change the Final number, and the difference is put into Adjustment automatically.

In the EP environment which is where the client is coming from, this works well. While the first two items have been completed and functionally were actually much easier to create in TM1, the third has proven to be problematic and I have yet to be able to create a rule that accomplishes what the end users would like to have.

If anyone has suggestions, I'm all ears!
Mike

[declanr]

So you have already done the spreading portion of your problem?

The third section is quite simple and doesn't really require a third cube, just an extra dimension on the second one so values are spread straight to "preliminary" (although a third cube can be used if you want)... then TI the values from "preliminary" to "final" and a simple rule to work out adjustment:

['Adjustment']=N: ['Final'] - ['Preliminary'];


job done.

[mmckimson]

Yes, the spreading has been accomplished and works quite well.

I tried your approach before, and while it does force the adjustment to be the difference between FINAL and PRELIMINARY, I don't think I explained the situation clearly enough. What the end user wants of course is to use the following formula for Final (FINAL = ADJUSTMENT + PRELIMINARY) yet have Adjustment behave exactly as it does in your formula, which is the default behavior in the EP tools when data is presented in Contributor.

I can't really get this to happen in TM1. If I make FINAL a subtotal and post data to PRELIMINARY, it doesn't work.

Mike

[declanr]

Yes, the spreading has been accomplished and works quite well.

I tried your approach before, and while it does force the adjustment to be the difference between FINAL and PRELIMINARY, I don't think I explained the situation clearly enough. What the end user wants of course is to use the following formula for Final (FINAL = ADJUSTMENT + PRELIMINARY) yet have Adjustment behave exactly as it does in your formula, which is the default behavior in the EP tools when data is presented in Contributor.

I can't really get this to happen in TM1. If I make FINAL a subtotal and post data to PRELIMINARY, it doesn't work.

Mike

What I was actually suggesting is that you have "Final" as an N-Level element and initially TI the spread numbers into it.

"Adjustment" is a C-Level Element with children "Final" (weight 1) and "Preliminary" (weight -1)

So initially:

"Prelim" = X
"Final"= 0
"Adj" = -x

Then TI "prelim" to "Final":

"Prelim" = X
"Final" = X
"Adj" = 0


Then users can manually change the values in "Final" which will change "Adj" accordingly...

[mmckimson]

What I was actually suggesting is that you have "Final" as an N-Level element and initially TI the spread numbers into it.

"Adjustment" is a C-Level Element with children "Final" (weight 1) and "Preliminary" (weight -1)

So initially:

"Prelim" = X
"Final"= 0
"Adj" = -x

Then TI "prelim" to "Final":

"Prelim" = X
"Final" = X
"Adj" = 0


Then users can manually change the values in "Final" which will change "Adj" accordingly...

You are quite correct... worked perfectly and now I have another arrow in the quiver also it's not exactly earth shattering I'm sure to TM1 gurus.

Thanks for your interest and help

Mike

[mmckimson]

I lied... because I haven't done a good enough job explaining my requirements. While your methodology does work, my end users need to have data writable in FINAL as we've defined in real-time as they are entering data via TM1 web and are used to seeing real-time results. Your solution assumes that data is transferred to PRELIMINARY via a rule and FINAL via a TMI process, correct? I further assume that there is no way for a user to trigger the running a process from TM1 web when they open a particular cube?

Mike