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 »

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 175 times
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Tm1 Support with Cumulative Data

Post by jim wood »

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.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Tm1 Support with Cumulative Data

Post by Steve Rowe »

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.
Technical Director
www.infocat.co.uk
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 »

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