consolidatedavg not at all dimensions?

[sibbi82]

Hello,

i have a problem with the consolidatedavg function and hopefully anyone could help me to deal with it.

I have a sample cube with three dimensions. I attached a picture to demonstrate my problem.

1. Measure A should be consolidated as average along the time dimension, but shoul be consolidated as sum along the third dimension.
My rule looks like:
['Measure A'] = C:ConsolidatedAvg(0, '', !DimYear, !DimA, 'Measure A');

2. The average consolidation should consider 0 values as well. I read that it works with the parameter "0" in the above function. But it didn't.

Any hints would be wonderful. Thanks!

---
Tm1 Version: 10.1.1 Fp1

[tomok]

I have a sample cube with three dimensions. I attached a picture to demonstrate my problem.

1. Measure A should be consolidated as average along the time dimension, but shoul be consolidated as sum along the third dimension.
My rule looks like:
['Measure A'] = C:ConsolidatedAvg(0, '', !DimYear, !DimA, 'Measure A');

2. The average consolidation should consider 0 values as well. I read that it works with the parameter "0" in the above function. But it didn't.

I've never used this function before but a quick perusal of the documentation makes it clear that if you don't want the average computed for a particular dimension you have to specify a fixed node in that dimension that the rule should apply to. If you use the bang notation (!), for a dimension then the function will consolidate for that dimension. In your case you used !DimA so it is being averaged across that dimension. You should have used 'consol_elim' instead. As to the other problem with it skipping zeros, have you tried using 1 in the first parameter instead of 0?

[sibbi82]

Thx for a reply!

I changed the rule to
['Measure A'] = C:ConsolidatedAvg(0, '', !DimYear, 'cons_elem', 'Measure A');

But it changed nothing at the result. I tried also to work with the STET function. But didn't get it.


YEs, i tried it with value 1. But that didn't work neither.
Probably it was not clever to place two problems .... I would appreciate to fokus on my first problem, that makes me much more headache...

[LanceTylor]

I am not sure what your time dimension is like but you could just be more specific on the left hand side of the rule [{'Quarter 1','Quarter 2'...etc}, 'Measure A] = ConsolidatedAvg

OR

You could potentially use an IF Statement which can be used to ignore the 'Cons_Elem' for all months (N level elements in your time dimension)

IF( DTYPE('DimA',!DimA)@='C") & DTYPE('DimYear',!DimYear)@='N'),
STET,
ConsolidatedAVg(...)

Hope this helps

[sibbi82]

Thanks Lance!

The hint with the if Statement solved the issue for me. I did it that way:

['Measure A'] = C:
IF(
DTYPE('DimA',!DimA) @= 'N'
,ConsolidatedAVg(...)
,STET
);

Additionaly i had to change the dimension order in the cube, to get the right sum in the C nodes of both dimension ...

Helpful for me was this thread as well
http://www.tm1forum.com/viewtopic.php?f ... 7&start=20