overriding Consolidation with Max value instead of sum

[sathishh.mk]

Hi All,

We trying to achieve to get the Max value at consolidation based on product elements instead of TM1 sum.

We have other dimensions as well in original cube.

Please find below sample screen for reference.

Any help/Suggestions are much appreciated.


Regards,
Sathish

[EvgenyT]

Use ConsolidatedMax function e.g being...

['Revenue' ] = C: ConsolidatedMax(0,'Cube Name (or blank)',!Product_Dim,'Revenue');

Thanks

[overflow]

Would there not need to be an identification of level for Product in this?

This code may produce a value at 'All Products' of 2000. With the requirement of All products to sum the components, this might not produce the right result at the top level, but will fix the issue at the initial consolidation level.

['Revenue' ] = C: ConsolidatedMax(0,'Cube Name (or blank)',!Product_Dim,'Revenue');

[sathishh.mk]

Hi All

I am using "ConsolidatedMin" function to achieve the min value at consolidation,but it is not working for rule derived cells.

Please find attached screen shot for reference.

I tried with feeder also but no luck.

Is there any thing i am missed out.

Please help me on this.


Regards,
Sathish

[declanr]

Hi All

I am using "ConsolidatedMin" function to achieve the min value at consolidation,but it is not working for rule derived cells.

Please find attached screen shot for reference.

I tried with feeder also but no luck.

Is there any thing i am missed out.

Please help me on this.


Regards,
Sathish

What is your rule and cube structure?

[sathishh.mk]

Hi,

Please find below are my rules and attached screen shot.
I have two measures, one of which(WPD) rule derived and trying to achieve "ConsolidatedMin" at conslidation.
Other one i am loading through TI Process and using "ConsolidatedMin" , then its working fine.

Only Rule derived cells its not working.

Rules:

['WPD' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'WPD');

['GDPW' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'GDPW');

Regards,
Sathish

[babytiger]

Hi,

If you want to apply ConsolidatedMax/ConsolidatedMin to all level 1 consolidation for the product_dim, you can use a rule like this:

['WPD' ] = C: If(Ellev('Product_Dim',!Product_Dim)=1,ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'WPD'),continue);
==> this would only apply consolidatedmin to level 1 consolidation of product_dim, and sum for other levels.

But instead of all level 1, you may want to an attribute to identify those consolidated elements that need to use consolidatedmin.

[declanr]

Hi,

Please find below are my rules and attached screen shot.
I have two measures, one of which(WPD) rule derived and trying to achieve "ConsolidatedMin" at conslidation.
Other one i am loading through TI Process and using "ConsolidatedMin" , then its working fine.

Only Rule derived cells its not working.

Rules:

['WPD' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'WPD');

['GDPW' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'GDPW');

Regards,
Sathish

Your actual ConsolidatedMin() rule for 'WPD' looks fine. With the issue only occurring on an element where the n-level cells are rule defined I would take a guess and say that whatever rule you are using there is overriding your consolidation rule (or possibly some other rule that appears above it in the rules sheet.)

If you right-click on the cell and select trace calculation you will see what rule/calculation is applying to it, if it is not your ConsolidatedMin() rule then you will need to look at whatever rule it is and narrow that rule down to not apply to the section you want your ConsolidatedMin() rule to apply to OR possibly just swap the order of the rules in question but if that is the case; exactly which one of those suggestions would be best to fix it without detriment of the other rules etc would be impossible to guess without seeing the entire rule sheet etc.

But if that is the case and you understand the effects of ordering on rule execution then you should be able to sort it out yourself quite easily.

[sathishh.mk]

Hi Declan,

Thanks for reply.

I tried the consolidatedMin with simple rules and by changing the order of the rules.
Still consolidated min is not working for rule derived cells and it is working fine for TI loaded cells.

Please find below rules and cube trace calculation attached screen shot .
In the screen shot, the GDPW has loaded from TI Process and WPD has derived from rule.

Let me know if i am missing any thing.


Rules:

#WPD Calculation
['WPD'] = N: 1440 * 60 * ['GDPW'];

['WPD' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'WPD');

['GDPW' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'GDPW');


#WPD Feeders:
Feeders;
['GDPW'] => ['WPD'];

Regards,
Sathish

[declanr]

Hi Declan,

Thanks for reply.

I tried the consolidatedMin with simple rules and by changing the order of the rules.
Still consolidated min is not working for rule derived cells and it is working fine for TI loaded cells.

Please find below rules and cube trace calculation attached screen shot .
In the screen shot, the GDPW has loaded from TI Process and WPD has derived from rule.

Let me know if i am missing any thing.


Rules:

#WPD Calculation
['WPD'] = N: 1440 * 60 * ['GDPW'];

['WPD' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'WPD');

['GDPW' ] = C: ConsolidatedMin(2,'',!Product__Dim, _Dim2,!Date_dim3,_Dim4,'GDPW');


#WPD Feeders:
Feeders;
['GDPW'] => ['WPD'];

Regards,
Sathish

You can see in your trace calc window that it isn't executing the consolidatedmin rule for the 'WPD' element. So if the rules are exactly as you have specified my next check would be looking at your config file, it looks a lot like you don't have it set to allow different rules for N and C levels.

[sathishh.mk]

Thanks alot Declanr!!

After setting the "allow different rules for N and C levels = T" property in cfg file, the "ConsolidatedMin" working fine for rules derived cells also.

Regards,
Sathish

[declanr]

Hi,

I've looked at your reply in post " overriding Consolidation with Max value instead of sum'.
In version 10.2 we have functions Consolidate Min & Consolidate Max, but I we were supposed to achieve this functionality in TM1 v9.5.2 where in we do not have functions for Min and Max, can you please suggest an approach to achieve the min and max functionality.

Thanks,
Radhika

Above quote sent through PM - please post all requests for assistance in the main forum, I say this so that you are able to get a more rounded advice personally and so that the forum as a whole can gain from the question.

In specific regards to the query I seem to recall that the ConsolidatedMin function (and the other consol functions) were actually included in 9.5.2 anyway but were undocumented by IBM at that time. I don't have access to a copy of 9.5.2 at the moment though so perhaps someone else will be able to confirm???