three dimensions into single dimension and vice versa

[charan]

Hi,
Thank you very much for the site, helped me lot to know about TM1.
I have developed a scenario like in one cube i have dimension like: Customer,Account,Bands, Scenario(Plan),Months(Jan-Dec),Years,Employee Details.
In another cube I have only continuous time dimension months with years like(Jun-12 to Dec-14) here there is no scenario dimension, I have only dimensions: Customer,Account,Bands,Cont.Months, Employee Details.
I have written a rule in second cube, please find the attached I have to make this dynamic once i have met this requirement then once again like from cont.month dimension I have to distribute the cont.month data in another which has month,year and scenario dimension.

Thanks and regards

Excel 2007
TM1 9.5.2

[declanr]

I have absolutely no idea what you wanted from your question.

But based purely on the picture I presume you are having trouble due to pulling in the total year for every month as opposed to just the month's value.
If that is the case, substitute 'Total' for Subst ( !c.months, 1, 3 ) ... or put an attribute in 'c.months' which references the month elements from 'Months'.

And assuming you have other years you would also need to substitute the '2012' hardcoded in your rule to an attribute driven version.

[charan]

Yes

I tried giving attributes in Cont.Month dimension but still I am getting the consolidate month data in C.Month because there is no month dimension in Cont.Month Cube.
Even after giving consolidate I am getting the consolidate value for each month in target cube.(here i should give ATTRS function in month dimension after giving attribute I think this will be fine).

For Year How should I do because there is no Year Dimension in cont.Month cube and it should be dynamic .What ever YEAR selected in Employee details cube that should come accordingly on Cont.Month cube,how to make it dynamic.

Please suggest me.

Thanks and regards

[declanr]

Charan,

I am not sure you actually understood my last post so I am sorry for possibly confusing you further.


['Ecost']=N: DB('Employee_Details',!Customer,!Account,'Plan','Total','2012',!Bands,'Ecost');

could become:

['Ecost']=N: DB('Employee_Details',!Customer,!Account,'Plan',Subst ( !c.months,1,3) ,'20' | Subst (!c.months, Long (!c.months)-1 , 2) ,!Bands,'Ecost');

The above proves the concept of how you can get numbers from different dims.

However that would only work for the years 20xx and should you be such a damn fantastic developer that your model is still in use come 2100 there would be a problem

SO...

In the Dim C.Months have 2 attributes; call 1 'TraceMonth' and the other ''TraceYear'.

Populate as below:

c.Month TraceMonth TraceYear
June-12 --- Jun --- 2012
Jul-12 --- Jul --- 2012
Aug-12 --- Aug --- 2012

['Ecost']=N: DB('Employee_Details',!Customer,!Account,'Plan',Attrs('c.month',!c.month,'TraceMonth'),Attrs('c.month',!c.month,'TraceYear'),!Bands,'Ecost');


HTH



p.s Why on earth would you have June-12 in a different format to the rest of the elements in the dimension?

[charan]

thank you
In entering I have done a mistake sorry for that.

[charan]

Declanr, thank you.

[charan]

Declanr,
How to make that rule for plan and month dynamic like here we are giving attributes manually but in real scenario how to make it automatic.

One more doubt in Vice versa like from cont.months to plan, year and months dimension.Now I want to create new cube which has scenario,year and months dimension.Can please give me suggestion for getting data from cont. months dimension(jan-12,feb-12...) to cube which has plan,year and months three dimension.


['EmployeeName']=S: DB('Employee_Details',!Customer,!Account,'Plan',Attrs('c.month',!c.month,'TraceMonth'),Attrs('c.month',!c.month,'TraceYear'),!Bands,'EmployeeName');

this is the rule i Have tried for EmployeName but i am not getting data in the target cube.

Thanks and regards

[charan]

Any suggestion for getting the data from single cont.Month dimension in one cube,
to the cube which has three dimensions like plan,months,year.

Thanks and regards

[Duncan P]

Code: Select all

DB( ... , !month | '-' | SUBST(!year, 3, 2 ), ... )

or whatever, provided of course that you fix the problem with June mentioned by Declan.