Tm1 Support with Cumulative Data

Post Reply
Sergio85
Posts: 4
Joined: Thu Jan 11, 2018 4:33 pm
OLAP Product: Planning Analytics 2.0
Version: TM1 64bit
Excel Version: Excel 2013

Tm1 Support with Cumulative Data

Post by Sergio85 » Wed Sep 12, 2018 2:13 pm

Hi all,

Relatively new user to Tm1 and I need advise on setting up a cube to cumulate data based on the dimension set up that is pre-existing that I cannot change.

There is already a pre existing formula created that pulls data for the 'Monthly FY Total Data' (by month) consolidation based on data inputs from two other cubes. I am trying to create a formula that would be unique to the 'Monthly Cuml' consolidation that will calculate data by month (at an element level) based on the elements from the 'Monthly FY Total' consolidation. I have tried to create a rule based off of attributes to pull data to take 'Previous Month' and add it to 'Current Month' in order to derive to a total, respectively, with no success.

Here are the current rules created concerning the Monthly Cuml consolidtation

['Cuml Oct']=N: ['Oct']; (which works with no problem, I just do not want add a similar formula 11 more times)

[{'Cuml Nov','Cuml Dec', 'Cuml Jan', 'Cuml Feb'}]=N:DB('Cube Data',!Versions,
!Fys,!Job Type,!Payscale,!Amount,!Programs,ATTRS('Cumulative Data',!Cumulative Data, 'Current Month')) +
DB('Cube Data',!Versions,
!Fys,!Job Type,!Payscale,!Amount,!Programs,ATTRS('Cumulative Data',!Cumulative Data, 'Previous Month'));


Essentially, I am hoping to create on rule that will calculate the cumulative data as oppose to creating 12 different formulas.

Please see the attachment I have added to this thread to give a better visual representation on what I am trying to calculate.
Everything that is colored in blue-gray is considered a dimension and I am trying to create a formula to solve for yellow/gold.
I also displayed the attributes editor
(**the 'Monthly Cuml' consolidation goes throughout the FY and does not stop at 'APR Cuml')

Thanks in advance!
Tm1 Forum Support.xlsx
(15.25 KiB) Downloaded 11 times

User avatar
jim wood
Site Admin
Posts: 3626
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Tm1 Support with Cumulative Data

Post by jim wood » Wed Sep 12, 2018 3:29 pm

Moved to the forum. Can you please only post a topic once. All duplicates will be removed.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
Steve Rowe
Site Admin
Posts: 1837
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Tm1 Support with Cumulative Data

Post by Steve Rowe » Wed Sep 12, 2018 4:27 pm

Hi,

You should be doing this in the consolidations for the dimension not the rules, rules will be significantly slower to calculate than a consolidation and will also require more feeding.

You might want to put a picture, instead or as well as the .xlsx as many people won't be able to download it to look at.

Sergio85
Posts: 4
Joined: Thu Jan 11, 2018 4:33 pm
OLAP Product: Planning Analytics 2.0
Version: TM1 64bit
Excel Version: Excel 2013

Re: Tm1 Support with Cumulative Data

Post by Sergio85 » Wed Sep 12, 2018 6:23 pm

Tm1 Support.JPG
Tm1 Support.JPG (204.04 KiB) Viewed 250 times
Adding Screen shot for further review

Post Reply