Flag value in consolidatedavg function

Post Reply
User avatar
macsir
Community Contributor
Posts: 652
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Flag value in consolidatedavg function

Post by macsir » Wed Jul 05, 2017 12:35 am

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
gtonkin
MVP
Posts: 739
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Flag value in consolidatedavg function

Post by gtonkin » Wed Jul 05, 2017 6:20 pm

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

User avatar
macsir
Community Contributor
Posts: 652
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Flag value in consolidatedavg function

Post by macsir » Thu Jul 06, 2017 12:22 pm

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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
macsir
Community Contributor
Posts: 652
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Flag value in consolidatedavg function

Post by macsir » Fri Jul 07, 2017 1:10 am

I can confirm same result in 10.2.2 FP5. No difference at all.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

MarenC
Posts: 52
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Flag value in consolidatedavg function

Post by MarenC » Sat Jun 29, 2019 12:09 pm

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
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Flag value in consolidatedavg function

Post by Wim Gielis » Sun Jun 30, 2019 5:42 pm

MarenC wrote:
Sat Jun 29, 2019 12:09 pm
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
Attachments
01.png
01.png (40.92 KiB) Viewed 498 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

MarenC
Posts: 52
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Flag value in consolidatedavg function

Post by MarenC » Wed Jul 03, 2019 5:57 pm

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
TestAVG.PNG (34.58 KiB) Viewed 454 times
Then with undefvals in the rule:
TestAVG_Undefvals.PNG
TestAVG_Undefvals.PNG (35.41 KiB) Viewed 454 times
Then with Undefvals commented out:
TestAVG_CommUndef.PNG
TestAVG_CommUndef.PNG (35.99 KiB) Viewed 454 times
regards, Maren

Wim Gielis
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Flag value in consolidatedavg function

Post by Wim Gielis » Wed Jul 03, 2019 6:16 pm

First test without undefvals like me.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

MarenC
Posts: 52
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Flag value in consolidatedavg function

Post by MarenC » Thu Jul 04, 2019 10:30 am

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

Post Reply