TM1 Forum

Guys, good afternoon!

I have a consolidation issue and would like your help.

Someone has had to realize this situation.

According to print, I have two dimensions (02.Functional_code and 02.MVS).

At the lowest level of functional code, the values I need are set.

When I go up to Functional Total, by hierarchical default, the values are summed in total.

But I need the values of the lowest level of functional code, as selected in red, to be replicated in Functional Total.

All the consolidation functions I have already used and did not give the expected result.

If anyone has any suggestions it will be of great help.

Show us your rules at C level.

You should think about your approach too, what you appear to be doing is likely to be very very inefficient.

You appear to want to see the same values at all levels on the function and MVS dimensions. Even each of the dimensions only has 10 members than you are holding these data points 100 times more often than you need to.

Either do the work in another cube without those dimensions

or (perhaps more practical)

Put a "No Function" and "No MVS" member in each dimension and do the work against only those elements. You can then allow the C level rules to naturally consolidate.

Hope this Helps.

I think I'm with Steve on this, but if this is the structure you want, have you tried the MAX() function? Assuming not all the Functional and MVS leaves are populated, that should work. If all are populated, then simply get the values of the first component in the Total Functional consolidation. The latter would be faster, I believe, in terms of execution time.

Wim Gielis wrote: ↑Thu Jan 02, 2020 7:22 pm Show us your rules at C level.

Hi Wim,

At level C I have only
['Functional Total', 'Park'] = C: ConsolidatedAvG (2, 'CMU.CALCULO_ESTOQUE_MVS_CALC',! 05.Version,! 05.Cenario,! 04.Month,! 04.Market,! 02 .Channel_Expenses,! 02.Group_companies,! 02.MVS,! 02.Functional_code, 'Park');

The rest of the rule is below:

['Park'] = N: IF (ATTRN ('04 .Year ',! 04.Year,' YEAR_NUMBER ')> DB (' CMU.PARAMETROS_CANAL_DESES ',! 05.Version,! 05.Scenario,' Apply Filters' ,! 02.Group_companies, 'P00', 'Year Ini') &

ATTRN ('04 .Year ',! 04.Year,' YEAR_NUMBER ') <DB (' CMU.PARAMETERS_Channel_DESES ',! 05.Version,! 05.Cenario,' Apply Filters',! 02.Group_companies, 'P00', 'Year Fin'),

DB ('CMU.PARQUE_FUNCIONAL',! 05.Version,! 05.Cenario,! 04.Month,! 04.Year, DB ('CMU.PARAMETERS_CANAL_DESES',! 05.Version,! 05.Cenario, 'Apply Filters' ,! 02.Company_Group,! 02.Channel_Expense, 'Reference Date'),

! 02.Market,! 02.Group_companies,! 02.Functional_code,! 02.MVS, 'QTY_VEI'), CONTINUE);



['Park'] = N: IF (ATTRN ('04 .Year ',! 04.Year,' YEAR_NUMBER ') = DB (' CMU.PARAMETROS_CANAL_DESES ',! 05.Version,! 05.Scenario,' Apply Filters' ,! 02.Group_companies, 'P00', 'Year Ini') &

ATTRN ('04 .Year ',! 04.Year,' YEAR_NUMBER ') = DB (' CMU.PARAMETERS_CANAL_DESHESE ',! 05.Version,! 05.Cenario,' Apply Filters',! 02.Group_companies,! 02.Channel_Expenses , 'Year Fin') &

ATTRN ('04 .Months',! 04.Months, 'Months')> = DB (' CMU.PARAMETROS_CANAL_DESESAS ',! 05.Version,! 05.Cenario,' Apply Filters',! 02.Group_companies, 'P00' , 'Month Ini') &

ATTRN ('04 .Months',! 04.Months, 'Months') <= DB (' CMU.PARAMETROS_CANAL_DESESAS ',! 05.Version,! 05.Cenario,' Apply Filters',! 02.Group_companies, 'P00' , 'Month Fin'),

DB ('CMU.PARQUE_FUNCIONAL',! 05.Version,! 05.Cenario,! 04.Month,! 04.Year, DB ('CMU.PARAMETERS_CANAL_DESES',! 05.Version,! 05.Cenario, 'Apply Filters' ,! 02.Company_Group,! 02.Channel_Expense, 'Reference Date'),
! 02.Market,! 02.Group_companies,! 02.Functional_code,! 02.MVS, 'QTY_VEI'), CONTINUE);



['Park'] = N: IF (ATTRN ('04 .Year ',! 04.Year,' YEAR_NUMBER ') = DB (' CMU.PARAMETROS_CANAL_DESES ',! 05.Version,! 05.Scenario,' Apply Filters' ,! 02.Group_companies, 'P00', 'Year Ini') &

ATTRN ('04 .Months',! 04.Months, 'Months')> = DB (' CMU.PARAMETROS_CANAL_DESESAS ',! 05.Version,! 05.Cenario,' Apply Filters',! 02.Group_companies, 'P00' , 'Month Ini') &

DB ('CMU.PARAMETROS_CANAL_DESESAS',! 05.Version,! 05.Cenario, 'Apply Filters',! 02.Group_companies, 'P00', 'Year Ini') <> DB ('CMU.PARAM_CANAL_DESESAS',! 05. Version,! 05.Cenario, 'Apply Filters',! 02.Group_companies, 'P00', 'Year Fin'),

DB ('CMU.PARQUE_FUNCIONAL',! 05.Version,! 05.Cenario,! 04.Month,! 04.Year, DB ('CMU.PARAMETERS_CANAL_DESES',! 05.Version,! 05.Cenario, 'Apply Filters' ,! 02.Company_Group,! 02.Channel_Expense, 'Reference Date'),
! 02.Market,! 02.Group_companies,! 02.Functional_code,! 02.MVS, 'QTY_VEI'), CONTINUE);



['Park'] = N: IF (ATTRN ('04 .Year ',! 04.Year,' YEAR_NUMBER ') = DB (' CMU.PARAMETROS_CANAL_DESES ',! 05.Version,! 05.Scenario,' Apply Filters' ,! 02.Group_companies, 'P00', 'Year Fin') &

ATTRN ('04 .Months',! 04.Months, 'Months') <= DB (' CMU.PARAMETROS_CANAL_DESESAS ',! 05.Version,! 05.Cenario,' Apply Filters',! 02.Group_companies, 'P00' , 'Month Fin') &

DB ('CMU.PARAMETROS_CANAL_DESESAS',! 05.Version,! 05.Cenario, 'Apply Filters',! 02.Group_companies, 'P00', 'Year Ini') <> DB ('CMU.PARAM_CANAL_DESESAS',! 05. Version,! 05.Cenario, 'Apply Filters',! 02.Group_companies, 'P00', 'Year Fin'),


DB ('CMU.PARQUE_FUNCIONAL',! 05.Version,! 05.Cenario,! 04.Month,! 04.Year, DB ('CMU.PARAMETERS_CANAL_DESES',! 05.Version,! 05.Cenario, 'Apply Filters' ,! 02.Company_Group,! 02.Channel_Expense, 'Reference Date'),
! 02.Market,! 02.Group_companies,! 02.Functional_code,! 02.MVS, 'QTD_VEI'), 0);

Thanks but first focus on the replies that others have written for you.
Also, the need to use numbers in dimension names escapes me. Why do you do that ?

Steve Rowe wrote: ↑Fri Jan 03, 2020 2:40 pm You should think about your approach too, what you appear to be doing is likely to be very very inefficient.

You appear to want to see the same values at all levels on the function and MVS dimensions. Even each of the dimensions only has 10 members than you are holding these data points 100 times more often than you need to.

Either do the work in another cube without those dimensions

or (perhaps more practical)

Put a "No Function" and "No MVS" member in each dimension and do the work against only those elements. You can then allow the C level rules to naturally consolidate.

Hope this Helps.

Hi Steve,

The problem is that for MVS the dimension has 3 levels.
In the image I left the totalizer hidden.
But could you better explain your suggestion anyway?

PavoGa wrote: ↑Fri Jan 03, 2020 7:12 pm I think I'm with Steve on this, but if this is the structure you want, have you tried the MAX() function? Assuming not all the Functional and MVS leaves are populated, that should work. If all are populated, then simply get the values of the first component in the Total Functional consolidation. The latter would be faster, I believe, in terms of execution time.

Hi Pavoga,

Already tried, but it did not work, because if he brings the MAX, will not meet the other items of the Hierarchy that have other values.
Since the hierarchy has 3 levels, the idea would be to replicate the values from level 0 to level 1 and this would be reflected in the "Functional Total".

Wim Gielis wrote: ↑Fri Jan 03, 2020 10:34 pm Thanks but first focus on the replies that others have written for you.
Also, the need to use numbers in dimension names escapes me. Why do you do that ?

Hi Wim,

Thanks for the answer.
I am evaluating each point and have been working on it for two days.
Regarding the number of dimensions, it is a development pattern that I use to separate measurement dimensions (01), hierarchical dimensions (02), nonhierarchical dimensions (03), time dimensions (04), version dimensions, and scenarios (05 ).
This was a standard used by the planning analyst.

aleferrer wrote: ↑Fri Jan 03, 2020 10:53 pmI am evaluating each point and have been working on it for two days.
Regarding the number of dimensions, it is a development pattern that I use to separate measurement dimensions (01), hierarchical dimensions (02), nonhierarchical dimensions (03), time dimensions (04), version dimensions, and scenarios (05 ).
This was a standard used by the planning analyst.

I wouldn't call it useless, but I see much more of an annoyance than practical usage. I have never seen this practice the last 12+ years.

Wim Gielis wrote: ↑Fri Jan 03, 2020 11:03 pm

aleferrer wrote: ↑Fri Jan 03, 2020 10:53 pmI am evaluating each point and have been working on it for two days.
Regarding the number of dimensions, it is a development pattern that I use to separate measurement dimensions (01), hierarchical dimensions (02), nonhierarchical dimensions (03), time dimensions (04), version dimensions, and scenarios (05 ).
This was a standard used by the planning analyst.

I wouldn't call it useless, but I see much more of an annoyance than practical usage. I have never seen this practice the last 12+ years.

I have been developing this way for about 10 years.
Never had any problems. So I'm following.rsrs

aleferrer wrote: ↑Fri Jan 03, 2020 10:37 pmHi Steve,

The problem is that for MVS the dimension has 3 levels.
In the image I left the totalizer hidden.
But could you better explain your suggestion anyway?

Create an element (numeric type, level 0) called 'No Function'. Add it as a child of 'Functional Total'.
That element contains the values that you now put on all the other elements. The other elements should not consolidate into the total, but the 'No Function' element should.

Wim Gielis wrote: ↑Fri Jan 03, 2020 11:28 pm

aleferrer wrote: ↑Fri Jan 03, 2020 10:37 pmHi Steve,

The problem is that for MVS the dimension has 3 levels.
In the image I left the totalizer hidden.
But could you better explain your suggestion anyway?

Create an element (numeric type, level 0) called 'No Function'. Add it as a child of 'Functional Total'.
That element contains the values that you now put on all the other elements. The other elements should not consolidate into the total, but the 'No Function' element should.

Hi Wim,

It worked.

Thanks to you and Stive for your help.