ConsolidatedAvg & UNDEFVALS
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
ConsolidatedAvg & UNDEFVALS
Hello friends,
i have to ask you something again....
Since 2 days i try to get ConsolidatedAvg working for me to replace a old "Counter/Ghost" logic to delete all helper elements.
I changed the server cfg, to add Zeroweightoptimization to the file, then i added "UNDEFVALS" to the Cube rule, there is no skipcheck in it.
Now i'm woundering if i had to add in every cell a zero or if there is an way to do that easier? Like add a standard value to a element.
Thanks for your help
Edit:
I forgot to tell you i try to get the average in the C Element but using input boxes for the N Elements.
i have to ask you something again....
Since 2 days i try to get ConsolidatedAvg working for me to replace a old "Counter/Ghost" logic to delete all helper elements.
I changed the server cfg, to add Zeroweightoptimization to the file, then i added "UNDEFVALS" to the Cube rule, there is no skipcheck in it.
Now i'm woundering if i had to add in every cell a zero or if there is an way to do that easier? Like add a standard value to a element.
Thanks for your help
Edit:
I forgot to tell you i try to get the average in the C Element but using input boxes for the N Elements.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hi,HighKeys wrote: ↑Wed Sep 04, 2019 7:21 am Hello friends,
i have to ask you something again....
Since 2 days i try to get ConsolidatedAvg working for me to replace a old "Counter/Ghost" logic to delete all helper elements.
I changed the server cfg, to add Zeroweightoptimization to the file, then i added "UNDEFVALS" to the Cube rule, there is no skipcheck in it.
Now i'm woundering if i had to add in every cell a zero or if there is an way to do that easier? Like add a standard value to a element.
Thanks for your help
Edit:
I forgot to tell you i try to get the average in the C Element but using input boxes for the N Elements.
could you post the rule for ConsolidatedAvg
I get scared.no skipcheck in it.
means, the form of your VBA?t using input boxes for the N Elements.
are the values in TM1 when you want a value on C-Level
what di you see in architect?
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hello
The AVG rule:
But i want the average in the C element of it.
Thanks & BR
Oliver
The AVG rule:
Code: Select all
[{'Urlaub %', 'Abwesenheit % Bedarf (Stand rMA)', 'Bezahlte Abwesenheit %', 'Meeting %', 'Unproduktive Anwesenheit %', 'Stundenkonto %', 'Krank %'}] = N:STET;
C:ConsolidatedAvg(1,'', !Tag, !Version, !MA-Pool, !Kennzahlen Personalbedarf-Planung);
Just for testing and Dev
Nope not the VBA project, its just a new cube that i try to build up, i enter some percentages manually directly in TM1 at the moment.
But i want the average in the C element of it.
Thanks & BR
Oliver
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
HighKeys wrote: ↑Wed Sep 04, 2019 10:09 am Hello
The AVG rule:
quick view locks good. btw. i don't like dim names with blanks - can cause troubleCode: Select all
[{'Urlaub %', 'Abwesenheit % Bedarf (Stand rMA)', 'Bezahlte Abwesenheit %', 'Meeting %', 'Unproduktive Anwesenheit %', 'Stundenkonto %', 'Krank %'}] = N:STET; C:ConsolidatedAvg(1,'', !Tag, !Version, !MA-Pool, !Kennzahlen Personalbedarf-Planung);
where in the rules file is your rule?
are there other rules for your KPIs - if so, what happens when the avg rule is the first one (then don't use STET use continue)
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
forgot:
what happens if you just try ist with one KPI eg "Urlaub %"
maybe it's more easy to investigate the mistake.
what happens if you just try ist with one KPI eg "Urlaub %"
maybe it's more easy to investigate the mistake.
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hi Orlando,
thanks!
the elements in the Rule like "Urlaub %" i just calulate this line with them.
It works as i can see correctly, if i add "0" to every blank cell.... what i dont understand, is it really needed that i add for everyday manually a 0 into the cell for the whole year to get it right or is there a possiblity to set a "Standard Value" like 0
I tried to find it on google or in the docs, but may i just not good at searching
Thanks again and BR
Oliver
Edit:
Let me show you some screens, may its easier to understand what i mean. i think its very basic what i ask
This is how the cube was before i added the "UNDEFVALS" to the Rule:
This is with:
Do i really have to add "0" manually in every cell? (Its not just the few one on the screen)
thanks!
the elements in the Rule like "Urlaub %" i just calulate this line with them.
It works as i can see correctly, if i add "0" to every blank cell.... what i dont understand, is it really needed that i add for everyday manually a 0 into the cell for the whole year to get it right or is there a possiblity to set a "Standard Value" like 0
I tried to find it on google or in the docs, but may i just not good at searching
Thanks again and BR
Oliver
Edit:
Let me show you some screens, may its easier to understand what i mean. i think its very basic what i ask
This is how the cube was before i added the "UNDEFVALS" to the Rule:
This is with:
Do i really have to add "0" manually in every cell? (Its not just the few one on the screen)
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hi,
you wrote about "Zeroweightoptimization" in the cfg.
What happens if you don't use this and dont't use UNDEFVALS
Just ConsAVG
I tried ist with PA 2.07 and it worked perfect.
I can remember that in older verion the function had a bug - but don't know if 10 or 9somethink
you wrote about "Zeroweightoptimization" in the cfg.
What happens if you don't use this and dont't use UNDEFVALS
Just ConsAVG
I tried ist with PA 2.07 and it worked perfect.
I can remember that in older verion the function had a bug - but don't know if 10 or 9somethink
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hey,
if i dont use both, it ignores the 0 Values.
if i had a table like
0
0
5
0
0
5
The AVG is 5
if i dont use both, it ignores the 0 Values.
if i had a table like
0
0
5
0
0
5
The AVG is 5
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: ConsolidatedAvg & UNDEFVALS
Hi,
I experienced a bug with this, see here: https://www.tm1forum.com/viewtopic.php?t=13414
If your weightings are zero you will have to add the Zeroweightoptimization back in.
MarenC
I experienced a bug with this, see here: https://www.tm1forum.com/viewtopic.php?t=13414
If your weightings are zero you will have to add the Zeroweightoptimization back in.
MarenC
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hello,
i already set the Zeroweightoptimization at the server config, but is there no easy way to fill every empty cell with a Zero?
Thanks and BR
Edit:
BTW I'm using V 11.4.00000.21
i already set the Zeroweightoptimization at the server config, but is there no easy way to fill every empty cell with a Zero?
Thanks and BR
Edit:
BTW I'm using V 11.4.00000.21
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
you could write a process to do so.
what happens if you dont't use UNDEFVALS?
TM1 then doesn't know a difference between a blank cell and a zero that is imported
could you try to not use Zeroweightoptimization and instead use the element weightings
what does the rule do then
i would do the following:
- no UNDEFVALS
- no Zeroweightoptimization
- weightings in the dimensions
- save data
- restart modell
- try rule
i also found that:
https://www.ibm.com/support/pages/ibm-p ... -fix-lists
they fixed a problem in PA 2.07 - that's just the version i tried it with
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hello Orlando!
Thansk for this link, god bless you
I searched the bug fix list since days
I will update during the weekend to 2.0.8 i think it will fix my issue, i will give u feedback after that.
Thanks!
Thansk for this link, god bless you
I searched the bug fix list since days
I will update during the weekend to 2.0.8 i think it will fix my issue, i will give u feedback after that.
Thanks!
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: ConsolidatedAvg & UNDEFVALS
Hi,
When I posted my comment I thought you had taken undefvals back out again? So why do you have to populate every empty cell with zero? I also thought you said you took out Zeroweightoptimization as per advice from Orlando?
All I was saying is if you have some weightings as zero and you want to include these in the average (which I presume is why you had Zeroweightoptimization in the first place) then you would need to add Zeroweightoptimization back in.
So what happens with no UNDEFVALS but Zeroweightoptimization=f?
Sorry if I misunderstood.
MarenC
When I posted my comment I thought you had taken undefvals back out again? So why do you have to populate every empty cell with zero? I also thought you said you took out Zeroweightoptimization as per advice from Orlando?
All I was saying is if you have some weightings as zero and you want to include these in the average (which I presume is why you had Zeroweightoptimization in the first place) then you would need to add Zeroweightoptimization back in.
So what happens with no UNDEFVALS but Zeroweightoptimization=f?
Sorry if I misunderstood.
MarenC
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hi MarenC!
With no UNDEFVALS but Zeroweightoptimization=F its ignores the 0 Values in the AVG.
BR
With no UNDEFVALS but Zeroweightoptimization=F its ignores the 0 Values in the AVG.
BR
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
for example, i forecast overtime too, but its usually not in so its a 0 most time, but sometimes its not good to get new Employees into the company cause we just have a peak at a day or week, so i add x% overtime to the calculation.
Its just one example where i have 0 Values.
Other example, Sickness, if no agent is Sick i have a Zero in there
Maybe i'm wrong and i build a worste cube cause i'm new to the lovely world of TM1
Its just one example where i have 0 Values.
Other example, Sickness, if no agent is Sick i have a Zero in there
Maybe i'm wrong and i build a worste cube cause i'm new to the lovely world of TM1
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
HighKeys wrote: ↑Thu Sep 05, 2019 11:27 am for example, i forecast overtime too, but its usually not in so its a 0 most time, but sometimes its not good to get new Employees into the company cause we just have a peak at a day or week, so i add x% overtime to the calculation.
Its just one example where i have 0 Values.
Other example, Sickness, if no agent is Sick i have a Zero in there
Maybe i'm wrong and i build a worste cube cause i'm new to the lovely world of TM1
We talk past each other.
The weighting means how an element is calculated into the next higher element.
It is not about whether the value is 0.
As an example
Everything has a weight of 1
Total 200
- Child1 100
- Child 2 100
Child 2 has a weighting of 0
Total 100
- Child1 100
- Child 2 100
Child 2 has a weighting of 0.5
Total 150
- Child1 100
- Child 2 100
I usually use this to display values in TEUR.
EUR has a weighting of 0.001
TEUR 10
- EUR 10.000
-
- Posts: 117
- Joined: Fri Aug 09, 2019 10:11 am
- OLAP Product: TM1 / TM1 Web / Perspectives
- Version: Planning Analytics V2.0.9
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
Hello Orlando,
sorry i was a some time not here, cause i had to calculate the Budget for the next year and no time for TM1 developing
Now i got your point, the weightings are mostly "1" but it dont help me with an average or?
Edit: if you mean i could set the weighting to 30 or 31 i think it will not work here, cause i try to build an avg over the montly value
BR
sorry i was a some time not here, cause i had to calculate the Budget for the next year and no time for TM1 developing
Now i got your point, the weightings are mostly "1" but it dont help me with an average or?
Edit: if you mean i could set the weighting to 30 or 31 i think it will not work here, cause i try to build an avg over the montly value
BR
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
depends on what you want..... like always in live
why are the weightings mostly 1 and not allways?
noooooooo. don't do that. that will not help.....Edit: if you mean i could set the weighting to 30 or 31 i think it will not work here, cause i try to build an avg over the montly value
i'm afraid that you didn't get the point for what the weighting is for. you should read the documentation for TM1 (i think you got the links in one of your other threads)
please do the following:
- update to PA 2.07 or better 2.08
- don't use UNDEFVALS in Rules
- don't use Zeroweightoptimization
- set the weightings for the elements you want to calc the avg for to 1
- check if you get trouble in other cubes this dimensions is used
- write the avg rule
- check the results
Question: Do you know where you find the setting for the weighting?
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: ConsolidatedAvg & UNDEFVALS
depends on what you want..... like always in live
why are the weightings mostly 1 and not allways?
noooooooo. don't do that. that will not help.....Edit: if you mean i could set the weighting to 30 or 31 i think it will not work here, cause i try to build an avg over the montly value
i'm afraid that you didn't get the point for what the weighting is for. you should read the documentation for TM1 (i think you got the links in one of your other threads)
please do the following:
- update to PA 2.07 or better 2.08
- don't use UNDEFVALS in Rules
- don't use Zeroweightoptimization
- set the weightings for the elements you want to calc the avg for to 1
- check if you get trouble in other cubes this dimensions is used
- write the avg rule
- check the results
Question: Do you know where you find the setting for the weighting?