How to consolidate dimension with maximum of children?

[abcuser]

Hi,
I have looked at the command ConsolidatedMax at: https://www-304.ibm.com/support/docview ... s=swgimgmt but don't quite understand how should I write (if I decide to upgrade to v9.5.2) formula for my problem (first post in this thread) using this function.

Just to refresh my problem. I have a dimension "reclaim_defect" with three levels:
- reclaim_defect (single top member),
- defect groups,
- defects.
There are only three levels in reclaim_defect dimension, but members in this groups can change. So I need something like:
- reclaim_defect: maximum value of defect_group level,
- defect groups: maximum value of all defects in current defect group.

Any idea how to write this kind of formula? Do I need to write a formula for every defect group? Defect groups can change, so I can't just write "hard-code" formulas in rules.
Regards

[abcuser]

Hi,
I have installed TM1 v9.5.2 and I have tried formula:

Code: Select all

['QUANTITY'] = C:ConsolidatedMax(0, '', !reclaim_buyer , !reclaim_defect, 'QUANTITY', !reclaim_product );

This kind of formula colsolidates ALL of dimensions as maximum of children. How to write such a formula that only dimension "reclaim_defect" will have a logic of maximum of children, but other two dimensions like reclaim_buyer and reclaim_product will use a sum of children consolidation?

See attached picture for simple sample.
P.S. I suggest to open picture in another window.
Regards

[Andy Key]

You can test the level of the reclaim_defect you are on, and only perform the ConsolidatedMax if you are on a consolidated reclaim_defect:

Code: Select all

['Quantity'] = C: 
   If( ElLev( 'reclaim_defect', !reclaim_defect) > 0,
      ConsolidatedMax( 0, '', !reclaim_buyer , !reclaim_defect, 'QUANTITY', !reclaim_product ),
      Continue); 

You may need to replace the Continue with a ConsolidateChildren.

[David Usherwood]

Not that I've played with ConsolidatedMax (d@^% client work keeps getting in the way) but wouldn't STET be better?
And I'm also leery of ConsolidateChildren - in some versions this hit performance quite heavily.

[abcuser]

Andy, I have tried your code and it does not produce correct values. I am quite a newbe at TM1 server. Can you please write what do you mean by ConsolidateChildren?

I am attaching a picture: green rectangles correct values, red rectangles incorrect values.
I am also attaching zip file with three .dim files and a .cub file to reproduce the sample.

Any help is greatly appreciated.
Thanks

[abcuser]

Hi,
I have solved the problem. Solution is bellow formula. Dimension order should also be changed!!! by Cube | Re-order dimensions and move dimension reclaim_defect to the LAST bottom position. The order ob dimensions is vital, if not reordering dimensions the result is not correct.

Code: Select all

['QUANTITY'] = C:
if(
	ELLEV('reclaim_buyer', !reclaim_buyer)= 0 &
	ELLEV('reclaim_product', !reclaim_product)=0
	,
	ConsolidatedMax(0,'', !reclaim_buyer, !reclaim_defect, 'QUANTITY',!reclaim_product)
	,
	STET
  )
;

Little bit more explaining: Getting deeper into the problem I have found out that I need to redefine requirement! Why? Because summing of one dimension and maximizing on another does NOT produce the same value (I haven't expected this behavior at all)! Look at attached picture. If aggregation is done using maximum of children on relcaim_defect dimension then max of 2800 and 2000 is 2800 (see red rectangles), but if default sum aggregation on buyer dimension is done then summing 2500 and 500 is equal to 3000 (see green rectangles). So aggregations on reclaim_defect and reclaim_buyer does not produce the same value! I have discussed with end-user and found out that maximum of children on reclaim_defect dimension should only be done (on all levers of reclaim_defect) but only on the lowest levels of other dimensions (level = 0). For upper-levels of dimensions buyer and product there should be a sum aggregation.
Regards

[abcuser]

Hi,
this simple sample is working fine. I have tried to do the same in production like cube which has more dimensions and more measures, but the LOGIC IS THE SAME and this production cube takes hours to display the result.

I am opening a new thread for this performance problem: How to write feeders for ConsolidatedMax? (Just applying to the forum rules: one post per problem).
Regards

[ioscat]

Code: Select all

skipcheck;
['Год']=C:DB('assum.ASO', !Версии, !Сценарии ASO, ELCOMP('Численность населения',!Численность населения,ELCOMPN('Численность населения',!Численность населения)), !Каналы, !Регионы, 'Год.Техн.');

['Год.Техн.'] = MAX (['Год'],
				DB('assum.ASO', !Версии, !Сценарии ASO, 
					IF (ELPARN ('Численность населения',!Численность населения)<>0, 
							DIMNM ('Численность населения', (DIMIX ('Численность населения',!Численность населения)-1)),'NULL' ), !Каналы, !Регионы, 
					'Год'));

let it be here