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 2 times

User avatar
jim wood
Site Admin
Posts: 3590
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: 1790
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 61 times
Adding Screen shot for further review

Post Reply