Flag value in consolidatedavg function
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Flag value in consolidatedavg function
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
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Flag value in consolidatedavg function
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
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
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Flag value in consolidatedavg function
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.
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
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Flag value in consolidatedavg function
I can confirm same result in 10.2.2 FP5. No difference at all.
-
- Regular Participant
- Posts: 355
- 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
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
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
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Flag value in consolidatedavg function
Hello Maren,
For me it works fine. PAL 2.0.7
- Attachments
-
- 01.png (40.92 KiB) Viewed 3842 times
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Regular Participant
- Posts: 355
- 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
Hi Wim,
thanks for taking the time to respond to this,
Here are my screen prints:
First with the rule as you have it:
Then with undefvals in the rule:
Then with Undefvals commented out:
regards, Maren
thanks for taking the time to respond to this,
Here are my screen prints:
First with the rule as you have it:
Then with undefvals in the rule:
Then with Undefvals commented out:
regards, Maren
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Flag value in consolidatedavg function
First test without undefvals like me.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Regular Participant
- Posts: 355
- 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
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
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