Searching for workaround to copy values from Excel to C:cell

[eruecker]

Hello all,

I am just in the process of migrating 7.3 Cognos Planning apps to TM1.

But now I am experiencing issues with the copy/paste from Excel in TM1 Contributor.

My finance users want to copy data from Excel and paste it into TM1 cube view.
There are users that copy the data for the next 12 months (Apr- Mar) , they just paste it into the cube viewer.

For the others , who want to just to copy a number into the consolidated cell and breakback to the months, it does not work.
In TM1 Contributor they can enter data in Total's column , but they insist on copy and paste from Excel.

The cube view looks like this:
LTM Actuals Previous FY FC Total (C) Apr (n) -------Mar (n)
Measure 1
Measure 2
Measure 3
etc.


Because it is only a group of 12 associates I wanted to make them Data Admin's or Admin's but
also they are not able to copy/paste into a C: level.

I thought about to bring in a new element in the Time dim.
Total Input as N-column.
Making two input subset views : one only for Total Input (copy /paste a year value in) and one for those who copy 12 months of data in.
So then the Analyst can decide which one the views he/she wants to use.

But how would I bring that together?

Total INput ---Rule---> Total (C:) , year data should be broken back to the 12 months .

Apr (n)---Mar(n) ------> Total (C:)

Could you please help me? Looking forward to your replies.

TIA.
Erik

TM1 9.5.2
Windows 2008
Excel 2003

[lotsaram]

I thought about to bring in a new element in the Time dim.
Total Input as N-column.
Making two input subset views : one only for Total Input (copy /paste a year value in) and one for those who copy 12 months of data in.
So then the Analyst can decide which one the views he/she wants to use.

But how would I bring that together?

Total INput ---Rule---> Total (C:) , year data should be broken back to the 12 months .

Apr (n)---Mar(n) ------> Total (C:)

I think you are on the right track. You should be able to achieve what you want by adding an additional "Total Year Input" N element into your month dimension. Then you just need a rule for the real months to check if there is a value in total year input and if there is then apply the spread (breakback) and if not then STET. This would allow your users both options of entering or copy/paste from excel into the months themselves or to paste into the full year and automatically break back to the months.

If I have understood correctly then this is what you want. The only thing your users would need to be aware of is that entry into the full year input would take precedence over individual month input.

[eruecker]

Hi Lotsaram,

thanks for your reply. I will test this.

Regards,
Erik

[eruecker]

Hello Lotsaram,

It worked.
Thanks a lot.
The code is not the best one, but could be improved.
Code is attached.

[tomok]

This works but why have 12 statements when one will do the trick:

Code: Select all

['Jan']= N:IF((ABS(['Total Year Input']) <> 0),(['Total Year Input']/12),STET);
['Feb']= N:IF((ABS(['Total Year Input']) <> 0),(['Total Year Input']/12),STET);
['Mar']= N:IF((ABS(['Total Year Input']) <> 0),(['Total Year Input']/12),STET);
['Apr']=N:IF((ABS(['Total Year Input']) <> 0),(['Total Year Input']/12),STET);
...

Assuming that the months are leafs in your time dimension and these are the only leafs in there except the Total Year Input.

Code: Select all

[]=N:
IF(!TimeDimName)@<>'Total Year Input'),
    IF(ELLEV(TimeDimName,!TimeDmName)=0,
       IF((ABS(['Total Year Input']) <> 0),(['Total Year Input']/12),
           STET),
       STET),
    STET);

Not sure what you are trying to accomplish here. If Total is the rollup of each month then this feeder is a waste. What I don't see is the feeder you need for the movement of data from the Total Year Input to the months.

Code: Select all

['Apr']=>['Total'];
['May']=>['Total'];
['Jun']=>['Total'];
['Jul']=>['Total'];
....

Assuming that the element Total means the rollup of the months. I see a lot of more suspicious feeders in your rules but not knowing anything more about the model I cannot comment.

Code: Select all

[''Total Year Input']=>{'Total'];

[eruecker]

Hi Tomok,

thanks for your reply.
My time dimension has these values:
Total Year Input (n-element)/Total (C:element)/Apr (n-element)....../Mar (n-element)/Prev FY FC/ LTM Actuals

I wanted to achieve that the value of the Total Year Input will be distributed to the months.
But for some reason , It worked only in one row (normal rollup of APR-MAR to Total).
For Work Order Variances and Purchase Price Variances it did not.
So I put these feeders in. APR >Total.
What is not working now , is the Throughput (only sums up the values from the derived rules, not the ones which are entered at the N-level (Apr-Mar)

This is my first project in TM1.
Unfortunately, I do not know now how to distribute the Total Input to the relevant months.
So I came up with this simple solution.
Thanks for showing me the trick.

How should I proceed?

Regards,
Erik

[lotsaram]

A few additional pointers that might be of help.

Testing for absolute value of 'total year input' doesn't really have a point. Either there is a non-null value you want to spread or the value is null. The test may as well just be IF( ['Total Year Input'] <> 0, ...

Also you seem to have a few mono-dimensional additive rules on the account dimension which is generally not something you want to do as consolidations are much more efficient (100x faster to calculate according to various pieces of documentation) and don't require rules or feeders. Your rules for 'Total Revenue' and 'Total Variable Costs' for example could be replaced with consolidations which would be the usual way of doing it. Rules should be reserved for calculations that you can't do with consolidations.

In addition to the approach for spreading 'total year input' to the months that Tomok showed you there is also an array notation that you can use in the LHS area statement only with square bracket notation. Using this would be more efficient computationally since no additional logical tests or functions would be needed it would just be a straight assignment of calculation to a cell. (Whether this probably small degree of efficiency would make a difference might be a moot point, but it's something to be aware of.)

Code: Select all

[{'Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb','Mar'}]=N:
IF( ['Total Year Input'] <> 0),
  ['Total Year Input']/12,
  STET
);

Rather than just dividing by 12 you can (and eventually should) get more sophisticated with spread methods. Usually in such a model where users can input a whole year as a single value you would have an assumptions cube bound by year, account, department/BU where the user select a method to spread the value across the months based on a picklist of string values. Common choices would be even spread, days in month, workdays in month, headcount, as per revenue, as per prior year same account, ... You may then have other auxiliary cubes holding some of these figures and based on the selection of phasing assumption the rule (which will obviously be a little more complicated) distributes the phasing differently to the months. Food for thought.

[paulsimon]

Have you tried Right Clicking and using the Spreading options? They are like break back.

Regards

Paul Simon

[eruecker]

Hi all,

thank you for your comments and heading me into the right direction.
Finally, I came up with this:

SKIPCHECK;

['Total Year Input'] = N: STET;

[]=N:IF(ELISCOMP('DIM_TIME',!DIM_TIME, 'Total') = 1
& ['Total Year Input'] <> 0,(['Total Year Input']\12),Continue);




['TVC in %']= ['Total Variable Costs']\['Total Revenue'];

['Throughput'] = ['Total Revenue' ] - ['Total Variable Costs' ] ;

[]=N:IF(ELISCOMP('DIM_TIME',!DIM_TIME, 'Total') = 1
& ['Total Year Input'] <> 0,(['Total Year Input']/12),STET);





FEEDERS;
#----------LOCAL FEEDERS------------------

['Total Year Input'] => ['Total'];
['Total Revenue'] => ['TVC in %'];
['Total Variable Costs'] => ['TVC in %'];
['Total Revenue'] => ['Throughput'] ;
['Total Variable Costs'] => ['Throughput'] ;

#---------DB FEEDERS-----------------------

The case is now answered successfully.
Regards,
Erik