[TM1 10.2.2] Avg&Sum Consolid on multiple dim [SOLVED]

[f3ar87]

Hi everybody! I'm on TM1 10.2.2, 64bit architecture and Excel 2007.

I have a problem while aggregating some data that have different criteria on different dimensions. I have found something similar in the forum but nothing that can solve my problem.
Here is the wrong situation that i have at the moment:



While here is the kind of consolidation that i want:



As you can see on the part dimension numbers are actually summing up while i want them to aggregate using average, min or max function since its verified that numbers are always the same on the same article (as you can see).
Then when article is on the level 1 (element SER-FC3) i want the number to sum up the average, min or max previously calculated.

I tried before using CalculatedAvg function using this formula

Code: Select all

[Misure:'QTA_MQ'] = C:ConsolidatedAvg(0,'',!Scelta, !Tempo, 'QTA_MQ', !CdC, !Articolo, !Parte);

but while correct at level-0 article (cells 102056, 1Z and 102058, 1Z) for the dimension article at level-1 i had the average of the previously calculated average (that is 4.024,44 in the example above for the cell SER-FC3, 1Z). Look at the image below:



Any advice will be greatly appreciated!

Thanks in advance,
Leonardo

[jim wood]

The may be your consolidation path. Try setting this using ConsolidateChildren,

Jim.

[Duncan P]

I'm not sure that I would use ConsolidateChildren, as it doesn't play well with feeders.

If you are doing a ConsolidateChildren over e.g. four large dimensions with multiple levels of hierarchy in each, then in order to get the top level it will be repeating the ConsolidateChildren call at each of the children at each level of each hierarchy specified in the ConsolidateChildren call, regardless of whether these cells are fed or not.

There is another way.

I will assume that you want to take the average (or min or max) over the Part dimension and the sum over all other dimensions (Article, Tempo & CdC) except Scleta which I presume is Version and doesn't have any aggregates.

Create a separate cube like the first but without the Part dimension and set its N: level rule to be the ConsolidatedMin/Max/Avg function you want sourced from the first cube. This will do your min/max/avg but only over the Part dimension and the results will be leaf values in the second cube. Leave the aggregation to occur in the normal way on the second cube. In the original cube set the C: level rule to get its values from the second cube if 0 <> ELLEV('Part', !Part) else STET.

Feed the second cube in the normal way from the original.

Of course if your Part dimension has more than one level of parent you will need to create another version of it with just the parents as a flat list and use that in the second cube.

I hope this makes sense. I no longer have a copy I can try this out on.

[f3ar87]

Hi Duncan!

Thanks so much! Your solution is working perfectly! Did not think about it cause I am still a newbie with TM1.

Anyway, great suggestion that I hope can help also somebody else.

Thanks again,
Leonardo