Calc cube: project profile (relative time) v. calendar time

[jasonsacco]

I'm looking for suggestions on the best way to lay in a project profile (hrs) based on a certain project start month within the calc cube. The project start date is element 'Task Start Date', (shown on left - c_sub_project_setup). My outputs in this cube are to calculate headcount and expense, and send it to an output cube downstream that is at a higher level (project level) than the calc cube (sub project task level).

I've played with DIMNM and DIMIX on both timescales within my rule statements: t_months (calendar months), and t_project months (which is generic month 1, month 2 and so) on project timescale. I've also tried many combinations of "IF" statements with many embedded 'DB's' solely looking at the calc cube, as well as combinations involving the setup cube, and project profile cube....and no luck

I've also tried using the DNEXT function on separate line elements trying base off of the 'Task start Date'...and no luck there either.

I've built the calc cube a few different ways by putting the project timescale also in the calc cube, essentially two timescales (relative time) - project time versus calendar time...and still no luck. This is what the current print screen shows. My ID's on both timescales start at '1', and go through time correctly on both dimensions.

the dimensions that show "1", those are the lists representing the different projects. I believe you can ignore for what I'm trying to calc. I can bring in the data correctly based on the Project and sub project ID's.

I also have an alias on the calendar timescale that is equal to my project timescale

Jan 12 -> alias = Month 1
Feb-12 -> alias = Month 2...and so on.

Thank you all for your time and help. I greatly appreciate it!

[jim wood]

I'm not 100% certain I understand what you mean (But that has never stopped me in the past!! )

Personally I would do something like this:

Create project properties cube and add to it the estimated length and head count.

Also create several text attributes for the months. One +1 month, +2 months etc. If you have the start date and the estimated length in months you can use the month attributes to spread the planned cost across the months and also compare the current number of months etc.

TM1 has never been great with time so you have to get a bit creative,

Jim.

[jasonsacco]

Jim-

I wanted to thank you for your recommendation. I used your concept of adding text attributes on my t_month dimension as Month 1, Month 2, and so on, and filled in the correct profile for each month.

I wrote a rule that looks at the task start date, and then does a attrs lookup to the time scale and returns the appropriate profile based on the start month. It works like a charm.

Heres a snip it of code: I'm still working on how to make the code more dynamic. I'm currently hard coding the month below...@= 'Jan-12' and so on...still trying different combinations of DIMIX and/or DIMNM and I think converting is to string...if you have any thoughts, I'd greatly appreciate it!
-----------------------------------
#Region Generic Month

['Generic Month' ] =S: IF(DB('c_sub_project', !h_model_hierarchy, !h_position, !t_month, !l_project_list, !l_sub_project_list, 'Task Start Date') @= 'Jan-12', ATTRS('t_month', !t_month, 'Month 1'), CONTINUE);

['Generic Month' ] =S: IF(DB('c_sub_project', !h_model_hierarchy, !h_position, !t_month, !l_project_list, !l_sub_project_list, 'Task Start Date') @= 'Feb-12', ATTRS('t_month', !t_month, 'Month 2'), CONTINUE);

['Generic Month' ] =S: IF(DB('c_sub_project', !h_model_hierarchy, !h_position, !t_month, !l_project_list, !l_sub_project_list, 'Task Start Date') @= 'Mar-12', ATTRS('t_month', !t_month, 'Month 3'), CONTINUE);

and so on...all the way down the time scale.
thanks again!