Break-back in calculated cell in contributor or tm1 web

[Yasser Hossam]

Hello Dears ,

i have have that rule ['GrossSales'] =N:['Qty]*['Price'];

And i want to make Break-back in the GrossSales , That is , i want to be able to change the value of the gross sales ,and then the value of the quantity changed according to it.

it works in the tm1 architect ,

but i want to make it work in TM1Web and Contributor

Any one know how to make it ?

[Wim Gielis]

Hello Dears ,

i have have that rule ['GrossSales'] =N:['Qty]*['Price'];

it works in the tm1 architect ,

I doubt this very much. Are you sure?
Because if the cells are rules-calculated, input is not allowed.
Unless you did not show us all rules involved.

[lotsaram]

I doubt this very much. Are you sure?
Because if the cells are rules-calculated, input is not allowed.
Unless you did not show us all rules involved.

Hi Wim - no that's not true. From 9.1 TM1 allows spreading over rule defined cells and it has gotten a bit better and more reliable since then and can handle more complex rules. The gotcha to make it work is there can be only a single "dependent variable" or input to flex otherwise the spread will fail as the server won't know which variable to adjust in oder to hit the spread target.

For a simple rule like ['GrossSales'] = N: ['Qty]*['Price'] you can spread over Gross Sales and it works! But for it to work only one of either Qty or Price can allow input. If there is more than 1 flex variable then spread over a rule can't work.

Yasser - if this works in cube viewer then it should work in TM1 Web and Contributor. Make sure you are looking at exactly the same view, with the same user, with the same security applied. If this functionality works in some UI but not al then it may be bug.

[Wim Gielis]

Thanks Lotsa, useful information!
I stand corrected and have learned something new.

[PlanningDev]

Hey Lotsaram,
Can you clarify when you say no entry to one of the variables if this means READ access through security of if data reservation or hold would also provide the same result? I guess I could quickly test myself but hey if you already have tried then no reason to reinvent the wheel!

[lotsaram]

Hey Lotsaram,
Can you clarify when you say no entry to one of the variables if this means READ access through security of if data reservation or hold would also provide the same result? I guess I could quickly test myself but hey if you already have tried then no reason to reinvent the wheel!

I don't recall but if you do the test please inform in this thread. My intuition would be no as TM1 will examine the security permission where the spread is being performed and the update status of underlying cells will only come into play once the spread is in operation so without doing the experiment at that point the spread would fail. Like in a simple proportional spread over pure data if a user has write to a C element but only read or none to some underlying leaves with values then TM1 will allow the spread but the spread will fail as only underlying leaves with write will be changes so when the spread is evaluated the result value will not be the target so the spread will error and rollback. I guess its not quite the same scenario but could be a similar result it would be good if it could be managed this way though! Much easier than ruling half the inputs in to avoid possibility of wroting.

[Wim Gielis]

Hi

I don't want to hijack this thread, but I cannot get this spreading over rules calculated cells working.

Situation:

- 1 cube, 2 dimensions (Period and measures, measures being P, Q, R)
- A simple rule like: ['R']=N:['P']*['Q'];
- input on [January, P]: 10
[January, Q]: 20
[January, R] shows 200 (correct).
Now typing any number or doing a data spread on [January, R], fails, also on any consolidated level

I changed the rule to ['R']=N:['P']*5; to have only 1 dependent variable. No improvement.
I cannot adjust any cell other than pure data cells.

How can I set up this small example model? WHere should I type what ?

TM1 10.1.1

Thanks in advance!

Wim

[Edward Stuart]

I have similar experiences on 10.2, I have also restricted access to dimensions to test with a non admin user and tested in TM1Web with no success (so far)

[lotsaram]

Hi Wim - I don't have a value=price*qty example but a spread over simple arithmetic average works like in the example below.

Code: Select all

SKIPCHECK;

['Price'] = C: ['Price_N'] \ ['Price_Count'];
['Price_N'] = N: ['Price'] ;
['Price_Count'] = N: IF( ['Price'] <> 0, 1, 0 );

FEEDERS;
['Price'] => ['Price_Count'] ,['Price_N'] ;

You can spread over Price which at C is defined as the average of the underlying leaves not the sum and the underlying leaves of Price will change to reflect the new desired average.

Actually I'm not 100% sure that this works for something like value=price*qty as although spreading over a rule was a feature I've been aware of for a few years it isn't something I've ever had a need for. It could be that the "one flex variable" concept that I remembered applied to the rule cell being spread over and the variable to be changed being the same measure as opposed to only one variable able to be changed. Maybe someone will know the exact answer but the example above works in TM1 10.1 and CX 10.1.

[Wim Gielis]

Yes, this works, thanks.

Back on-topic now