CellPutN and CellGetN question

[moviedancer]

hope someone can help my question,
i am trying to calculate the proportion of each element of its parent;

to put out the data at lowest level;
vDimGL=CellGetN('Rpt_Dimension',dim01,'RawData',dim04,dim02,dim06,dim03,dim07,dim08,dim09,dim10);

copy over to a different column

vDimGL=CellGetN('Rpt_Dimension',dim01,'RawDatatotal',dim04,dim02,dim06,dim03,dim07,dim08,dim09,ELPAR(vDim1,dim10,1));

-584598.02 -584598.02 YTD Technical View Actual 12 Asia BU Reporting 111 2017-12 AAA 6710001011 Acquisition Cost
-289321.79 -873919.81 YTD Technical View Actual 12 Asia BU Reporting 111 2017-12 AAA 6710001014 Acquisition Cost

there is the output file based on above ti process.
it seems calculation was done by line by line

can someone tell how to fix this?
Thanks very much in advance

[Wim Gielis]

Hello

So your data source of the process is a cube view ?
In that case you will execute the code in the Data tab of the process for every cell in the source view.
Please elaborate more on your data source, do you skip consolidations / rules values / zeroes / ... ?

Can I ask you why you name your cube 'Rpt_Dimension' ?
The syntax regarding CellGetN seems fine.

The goal is to create a text file ? Then you won't need CellPutN.

[moviedancer]

sorry i had some typo in my first post.

no, my goal wasn't to create a output file but i created to test my script.

i had these two lines.

vDimGL=CellGetN('Rpt_Dimension',dim01,dim11,dim04,dim02,dim06,dim03,dim07,dim08,dim09,dim10);
vDimGLPar=CellGetN('Rpt_Dimension',dim01,dim11,dim04,dim02,dim06,dim03,dim07,dim08,dim09,ELPAR(vDim1,dim10,1));

ultimately i want to do vRatio=vDimGL/vDimGLPar to see the weighing of each element;


In this 'Rpt_Dimension' cube, there is a dimension with two elements, one was rawdata, the other one was tech_view
i want to copy the data from 'rawdata' to 'tech view', which is vDimGL


-584598.02 -584598.02 YTD Technical View Actual 12 Asia BU Reporting 111 2017-12 AAA 6710001011 Acquisition Cost
-289321.79 -873919.81 YTD Technical View Actual 12 Asia BU Reporting 111 2017-12 AAA 6710001014 Acquisition Cost

if you look at the output,

i thought the vDimGLPar will return the total of each element, which should be (584598.02 element 1+289321.79 element 2)=873919.81 but if you see the output, the column 2 raw 1 didnt give me what i was after.
any idea?

[Wim Gielis]

The output is not very clear if you don't mention what is in the AsciiOutput.
What data are we looking at ?
In what dimension do you want to take the value of parent - the last dimension of the cube ? vDim1 ?

Can you first rework this and use good variable names please instead of Dimxxx, vDim..., 'Rpt_Dimension' as the cubename, vDimGLPar / vDimGLPar (why not vAccount and vAccount_Parent for example ) ?
Which dimension has two elements, one was rawdata, the other one was tech_view ?

[moviedancer]

long story short,
i have a situation like this,
i wrote a ti process gave me a result like this ...which isnt what i was after

Parent Raw Parent_Total
Element1 10 10
Element2 10 20
Element3 10 30
Element4 10 40
Element5 10 50

i want to see

Parent Raw Parent_Total
Element1 10 50
Element2 10 50
Element3 10 50
Element4 10 50
Element5 10 50

that's why i use CellGetN (.......element);
and CellGetN(.......ELPAR(dimension,Element));

[Wim Gielis]

How can your parent value change (the CellGetN) if you don't use CellPutN ....... ?

[moviedancer]

this is a part of my calcuation coz i want to calculate the weighting of each element

can TI process give me the result 1/5 for each element?

Element1 10
Element2 10
Element3 10
Element4 10
Element5 10

[moviedancer]

am i using the formula incorrectly? sorry i am not a developer, but an accountant,

this is cube view

Element1 10
Element2 10
Element3 10
Element4 10
Element5 10

and i use cellgetN(.........ELPAR(dimension, element))
all element 1-5 are under same parents,

i thought this will return 50 for each element?

but my goal is to spread some value back to these five elements by proportion.

[Wim Gielis]

Here is some dummy code for an allocation:

Code: Select all

vAccount = NValue;
vAccount_Parent = CellGetN('CubeName', elem01, elem02, elem03, elem04, elem05, ..., ELPAR( vDim, elem6, 1 ));

vRatio = vAccount \ vAccount_Parent;
vToBeAllocated = CellGetN( ..., ..., ... );
vAllocated = vToBeAllocated * vRatio;

CellPutN( vAllocated, 'CubeName', elem01, elem02, 'SOMEWHERE ELSE', elem04, elem05, ..., elem6 );

The cube view gives you values (using a CellGetN or simply NValue or Value or whatever you named the variable in the Variables tab of the process). Then you retrieve the value of the parent.
Get the value to be allocated (could be a different cube).
Calculate the value to be allocated as the multiplication.
Write the result in the cube.

[tomok]

If you want to allocate something back to a cube, based on the current values in the cube, you can use the TI function CellPutProportionalSpread. You can read about in the documentation but it sounds like what you need.

[moviedancer]

Here is some dummy code for an allocation:

Code: Select all

vAccount = NValue;
vAccount_Parent = CellGetN('CubeName', elem01, elem02, elem03, elem04, elem05, ..., ELPAR( vDim, elem6, 1 ));

vRatio = vAccount \ vAccount_Parent;
vToBeAllocated = CellGetN( ..., ..., ... );
vAllocated = vToBeAllocated * vRatio;

CellPutN( vAllocated, 'CubeName', elem01, elem02, 'SOMEWHERE ELSE', elem04, elem05, ..., elem6 );

The cube view gives you values (using a CellGetN or simply NValue or Value or whatever you named the variable in the Variables tab of the process). Then you retrieve the value of the parent.
Get the value to be allocated (could be a different cube).
Calculate the value to be allocated as the multiplication.
Write the result in the cube.

thanks for your reply,
i think this is what i did,

under vAccount, it has five children (GL account);

vAccount = ( what i wrote was CellGetN('CubeName', elem01, elem02, elem03, elem04, elem05, ..., elem6);

vAccount_Parent = CellGetN('CubeName', elem01, elem02, elem03, elem04, elem05, ..., ELPAR( vDim, elem6, 1 ));

vAccount has 5 children, GL account 1-5; which return $10,$20,$30,$40,$50

vRatio = vAccount \ vAccount_Parent; ( i thought vAccount_Parent can return $150,$150,$150,$150,$150, which can give me ratio 1/15, 2/15,3/15,4/15,5/15);

then i'll allocate some value by vRatio;

i did the whole process i realised something went wrong, that's why i textoutput to see what happened,

[Wim Gielis]

Hello

It's important to understand that vAccount is the lowest level of detail. The values of 10 for each lowest level account will come by in the view of the data source. The values of the parent account will not come by in the view. You will use CellGetN to get those values of 150 for example. Then divide the 2 values. That's all there is to it... what is the problem you still face ? Can you understand the problem based on the TextOutput ? Is your view correct ? Is your zeroout correct?

[moviedancer]

Hello

It's important to understand that vAccount is the lowest level of detail. The values of 10 for each lowest level account will come by in the view of the data source. The values of the parent account will not come by in the view. You will use CellGetN to get those values of 150 for example. Then divide the 2 values. That's all there is to it... what is the problem you still face ? Can you understand the problem based on the TextOutput ? Is your view correct ? Is your zeroout correct?

yes vAccount is lowest level, vAccountParent is one level above.

Parent account is in ram which i understand that's why i textOutput to see what's in vAccountParent.

it gives me
vAccount vAccountParent
10 10
20 30
30 60
40 100
50 150

which gives me incorrect vRatio ;

but in theory and what i am after
it should return
vAccount vAccountParent
10 150
20 150
30 150
40 150
50 150
is that right?

[Wim Gielis]

It should indeed give you your second set of results and not the first set of results. I don't understand how you can get those results.

Does every account have 1 parent ? If not the case then ELPAR can give results you don't expect.

It looks like you do some cumulative calculation ?

So to make it concrete. You could have a cube with dimensions Year, Period, Company, Currency, Cost center, Account, Measure (just amount). Then for any month, year, cost center, ... the amount of the lowest level account (as part of the data source) is coming by, and with CellGetN you get the value of exactly the same combination but then for the parent account. I don't see what could go wrong here in fact. You don't change values you just retrieve them.

Show us screenshots of your code, cube and dimensions.

[moviedancer]

Here is the result

[Wim Gielis]

My best bet is that you run into trouble with the ELPAR as indicated above.
I guess that you have monthly values for a certain account and instead of getting the parent in the account dimension, you take a YTD parent.
Hence the kind of 'cumulative' calculation in there.
If that is not it and you verified the ELPAR is working 100% correct, add more screenshots of the cube (at level 0 and at the parent level), of the data source, Prolog tab, Variables tab, and so on.

[moviedancer]

My best bet is that you run into trouble with the ELPAR as indicated above.
I guess that you have monthly values for a certain account and instead of getting the parent in the account dimension, you take a YTD parent.
Hence the kind of 'cumulative' calculation in there.
If that is not it and you verified the ELPAR is working 100% correct, add more screenshots of the cube (at level 0 and at the parent level), of the data source, Prolog tab, Variables tab, and so on.

YTD came from a different cube, when i loaded data to this cube, it has been loaded to YTD which is lowest level;

[Wim Gielis]

Hello,

As asked, please provide clear and meaningul screenshots, otherwise I can continue my trial and error and that's not going to work

Why do you have a CellPutN ?
What is the data source of the process ? A different cube ?
What is the Prolog tab code ?
And so on.
Make the screenshots anonymous if that's why you are not showing them here.

[moviedancer]

Hello

So your data source of the process is a cube view ?
In that case you will execute the code in the Data tab of the process for every cell in the source view.
Please elaborate more on your data source, do you skip consolidations / rules values / zeroes / ... ?

Can I ask you why you name your cube 'Rpt_Dimension' ?
The syntax regarding CellGetN seems fine.

The goal is to create a text file ? Then you won't need CellPutN.

i think i know why

in prolog i have a viewzeroout to target view

if i comment out this, it will work.


why???

[Wim Gielis]

That really makes no sense (at least with the information we now have).

I am sorry, I will stop the help now.
You don't give out the necessary details, also not anonymous data, and such 'trial and error' guesswork on a sunny Saturday afternoon is not how I want to solve the problem.

I do understand the risk of stopping the assistance here, as JulianS might see this topic and make nasty comments to me in person, but anyway.