Page 1 of 1

Store values VS. Accumulate values

Posted: Sun Aug 06, 2017 7:46 pm
by sunlight
Hi all,

I got the basic concept how store values vs. accumulate values works, but I cannot explain the values from the 6 dimensions cube from the recipe from TM1 Cookbook (Ankit Garg).

Here is the value 55.704,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select store values:
store values cube.jpg
store values cube.jpg (92.76 KiB) Viewed 10022 times
here is the value 429.208,00 for "Quantity" in "January" for "Lanterns", "Budget Version 1", when I select accumulate values:
accumulate values cube.jpg
accumulate values cube.jpg (98.5 KiB) Viewed 10022 times
I checked the source excel file and got how the values sum up to 429.208 for accumulate values:
accumulate values excel source.jpg
accumulate values excel source.jpg (284.52 KiB) Viewed 10022 times
But I cannot get how the values sum up to 55.704 for store values, I marked these values yellow.

I would appreciate any idea on my example!


Excel file I am uploading is attached.

Re: Store values VS. Accumulate values

Posted: Sun Aug 06, 2017 7:53 pm
by sunlight
Link to the Yandex Drive with the Excel file "sales_plan2.xls":
https://yadi.sk/i/DRwYM6An3Lm4cS

Re: Store values VS. Accumulate values

Posted: Mon Aug 07, 2017 4:26 am
by gtonkin
Hi sunlight,
Accumulating is basically getting the value in the cube then adding to that the value in the incoming record. The result is then written back to the relevant cell (based on the data in the record e.g. Version, month, product etc.) in the cube.
Store is simply taking the value from the incoming record and writing it to the relevant cell. If your file has multiple values for the same comibinations, based on your dimensions, values will be over-written each time a value for the same cell in the cube is encountered.
In short, the last value encountered in your source file for a particular cell is written to the cube.

HTH

Re: Store values VS. Accumulate values

Posted: Mon Aug 07, 2017 8:23 am
by Wim Gielis
gtonkin wrote: Mon Aug 07, 2017 4:26 am Hi sunlight,
Accumulating is basically getting the value in the cube then adding to that the value in the incoming record. The result is then written back to the relevant cell (based on the data in the record e.g. Version, month, product etc.) in the cube.
Store is simply taking the value from the incoming record and writing it to the relevant cell. If your file has multiple values for the same comibinations, based on your dimensions, values will be over-written each time a value for the same cell in the cube is encountered.
In short, the last value encountered in your source file for a particular cell is written to the cube.

HTH
Good explanation from George.

Let me add to this regarding the codes that we use in Turbo Integrator.
You can use CellGetN( ) to get a value from a cube. Then you add a value, usually stored in a variable, representing the value in the incoming record.
After that you do a CellPutN( ) to write the value to the cube, overwriting whatever value is in the cube at that intersection.

This was the traditional approach before TM1 version 10.0 (but still works perfectly). Since version 10.0 we use the function CellIncrementN( ) which does the same task in 1 go. This means less code, less places in the code where fixed or hard-coded values are. I don't think it will matter with respect to performance.

For anyone using the Turbo Integrator wizard, this has not been updated with CellIncrementN( ) and still uses CellPutN( CellGetN( ) )

Re: Store values VS. Accumulate values

Posted: Mon Aug 07, 2017 10:21 am
by sunlight
Hi gtonkin,

thank you a lot for your reply!
After posting the question on forum it seems I found the answer. I just needed to sort the dimensions in "A to Z" order starting from the last one in the cube, then it becomes really obvious which values TM1 takes from this excel file in case of Store Values.

Re: Store values VS. Accumulate values

Posted: Mon Aug 07, 2017 5:08 pm
by tomok
sunlight wrote: Mon Aug 07, 2017 10:21 am Hi gtonkin,

thank you a lot for your reply!
After posting the question on forum it seems I found the answer. I just needed to sort the dimensions in "A to Z" order starting from the last one in the cube, then it becomes really obvious which values TM1 takes from this excel file in case of Store Values. Here is a short post with a little bit more details in my TM1 blog: https://tm1notes.wordpress.com/2017/08/ ... te-values/
You don't know the difference between accumulating and storing values in a TI process and you're writing a blog about how to use TM1? :roll:

Re: Store values VS. Accumulate values

Posted: Mon Aug 07, 2017 9:11 pm
by Sighurd
tomok wrote: Mon Aug 07, 2017 5:08 pm You don't know the difference between accumulating and storing values in a TI process and you're writing a blog about how to use TM1? :roll:
Ambitious, right? :mrgreen:

Re: Store values VS. Accumulate values

Posted: Tue Aug 08, 2017 9:08 pm
by sunlight
Hi Wim Gielis,

thank you a lot for your reply and information on CellIncrementN( ) !

I also compared TM1 scripts generated by wizard CellPutN( CellGetN( ) ), but was still curious how the calculation looks like on this real example "sales_plan2.xls".

Re: Store values VS. Accumulate values

Posted: Tue Aug 08, 2017 11:17 pm
by Wim Gielis
sunlight wrote: Tue Aug 08, 2017 9:08 pm I also compared TM1 scripts generated by wizard CellPutN( CellGetN( ) ), but was still curious how the calculation looks like on this real example "sales_plan2.xls".
Then it would be helpful if you could post your actual code. Also have a look at the help files for the arguments of that function.

Re: Store values VS. Accumulate values

Posted: Fri Aug 11, 2017 7:16 pm
by sunlight
Hi Wim Gielis,

here are the scripts generated by Wizard. Store Values, as you wrote it is CellPutN( ):
script store values.jpg
script store values.jpg (42.91 KiB) Viewed 9688 times
Accumulate Values, as you wrote it is CellPutN( CellGetN( ) ):
script accumulate values.jpg
script accumulate values.jpg (66.59 KiB) Viewed 9688 times
My question is actually already solved, thank you and @gtonkin for your help!

Re: Store values VS. Accumulate values

Posted: Sat Aug 12, 2017 3:31 pm
by Wim Gielis
Okay, so if you bypass the wizard code of CellPutN and CellGetN, it would be a CellIncrementN function.