Flag value in consolidatedavg function

[macsir]

Hi guys, I have tested this function with different flag value (0,1,2) when leaf elements are zeros or not. The result has no difference at consolidation level. Any idea? I am currently use 2 for safety.

[gtonkin]

Cannot say I see the same behaviour - on a 10.2.2 FP4 model I have. We have about 700 branches that we collect data on. Some data like Net Promoter Score is given as a score and we need to average it all the way up the hierarchy (as we cannot restate the score because we do not have the components.) Previously I would calculate the average as the sum of the scores divided by a count of branches with scores (written to a "Denominator" source as a 1 or 0 by TI process). The number of populated branches and score differs monthly may be between 590 and 650. Using this method I would derive an average branch score.

Using ConsolidatedAVG(2,...) gives me the same answer as the long-hand calculation i.e. takes the sum and divides by populated, non-zero branches.
Using ConsolidatedAVG(1,...) gave me the wrong answer for this measure as it took the sum across branches and divided by the number of leaves.

I am not sure what you have as your cube and dimension references in your rule but when I write these rules I basically type in the ConsolidatedAVG(2, piece then click on DB, select my cube, leave as All, OK then remove the DB( that it inserted and have my reference as I need it. HTH

[macsir]

Thanks, mate.
Yes, I am using same syntax as yours with testing on simplest cube but can't see any difference at higher level. That's why I am curious about it. Forgot to mention, I am in PA now. Will test it again once I am in 10.2.2.

[macsir]

I can confirm same result in 10.2.2 FP5. No difference at all.

[MarenC]

Hi,

Looked for help on this and this topic seemed the most relevant.

In Planning Analytics 2.06,

I created a simple cube with 2 dimensions and used the following code in a rule (the only rule in the cube):

['ELAvg']=N:
ConsolidatedAvg( 1, 'Cube', 'AreaA', 'ConsolElement');

Under ConsolElement I have 2 leaf level children, both weighted 1.

In the Cube I have the following values,

FirstChild = 30
SecondChild = 0

so the ConsolElement = 30

Whether I use the flag value 1 or 2 I get the average 30 i.e. ELAvg = 30.

I was hoping flag 1 would make ELAvg 15 and flag 2 would make it 30.

I then tried to add UNDEFVALS; to the rule to see if that would help.

I needed to make SecondChild = 0 again because adding UNDEFVALS; made it blank

This then changed the behaviour where whether I use the flag value 1 or 2 I get ELAvg = 15!

Then I commented out UNDEFVALS and it then appeared to work as I had expected, i.e.

Flag 1 made the ELAvg 15 and Flag 2 made ELAvg 30!

Then if I reverse the values and make FirstChild 0 and SecondChild 30 I have to do the whole thing again to get it to work as I expected

Can anyone please throw any light on the above? The above is me simply playing around with TM1 functionality, I liked the idea of creating averages that included and excluded zero values.

Regards, Maren

[Wim Gielis]

I was hoping flag 1 would make ELAvg 15 and flag 2 would make it 30.

Hello Maren,

For me it works fine. PAL 2.0.7

[MarenC]

Hi Wim,

thanks for taking the time to respond to this,

Here are my screen prints:

First with the rule as you have it:

TestAVG.PNG (34.58 KiB) Viewed 5017 times

Then with undefvals in the rule:

TestAVG_Undefvals.PNG (35.41 KiB) Viewed 5017 times

Then with Undefvals commented out:

TestAVG_CommUndef.PNG (35.99 KiB) Viewed 5017 times

regards, Maren

[Wim Gielis]

First test without undefvals like me.

[MarenC]

Hi Wim,

That is what I did, if you look at my first screen print in the comment above I first had the rule just as you had it, i.e. without undefvals.

The second and third images then show what happens when I use undefvals

regards, Maren