ConsolidatedAvg & UNDEFVALS

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

ConsolidatedAvg & UNDEFVALS

Post by HighKeys » 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.

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Wed Sep 04, 2019 9:37 am

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.
Hi,
could you post the rule for ConsolidatedAvg
no skipcheck in it.
I get scared.
t using input boxes for the N Elements.
means, the form of your VBA?

are the values in TM1 when you want a value on C-Level
what di you see in architect?

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Wed Sep 04, 2019 10:09 am

Hello


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);
orlando wrote:
Wed Sep 04, 2019 9:37 am
I get scared.
Just for testing and Dev :)
orlando wrote:
Wed Sep 04, 2019 9:37 am
t using input boxes for the N Elements.
means, the form of your VBA?
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

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Wed Sep 04, 2019 10:42 am

HighKeys wrote:
Wed Sep 04, 2019 10:09 am
Hello


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);
quick view locks good. btw. i don't like dim names with blanks - can cause trouble
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)

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Wed Sep 04, 2019 10:47 am

forgot:

what happens if you just try ist with one KPI eg "Urlaub %"
maybe it's more easy to investigate the mistake.

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Wed Sep 04, 2019 2:27 pm

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:
Stundenkonto without UNDEFVALS.PNG
Stundenkonto without UNDEFVALS.PNG (5.44 KiB) Viewed 586 times

This is with:
Stundenkonto UNDEFVALS.PNG
Stundenkonto UNDEFVALS.PNG (4.56 KiB) Viewed 586 times

Do i really have to add "0" manually in every cell? (Its not just the few one on the screen)

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Wed Sep 04, 2019 2:43 pm

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

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Wed Sep 04, 2019 2:55 pm

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

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

Re: ConsolidatedAvg & UNDEFVALS

Post by MarenC » Wed Sep 04, 2019 4:11 pm

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

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Thu Sep 05, 2019 6:36 am

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

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Thu Sep 05, 2019 6:52 am

HighKeys wrote:
Thu Sep 05, 2019 6:36 am
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
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

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Thu Sep 05, 2019 7:51 am

Hello Orlando!

Thansk for this link, god bless you :D

I searched the bug fix list since days :lol:

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!

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

Re: ConsolidatedAvg & UNDEFVALS

Post by MarenC » Thu Sep 05, 2019 8:09 am

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

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Thu Sep 05, 2019 9:36 am

Hi MarenC!


With no UNDEFVALS but Zeroweightoptimization=F its ignores the 0 Values in the AVG. :(


BR

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Thu Sep 05, 2019 9:42 am

HighKeys wrote:
Thu Sep 05, 2019 9:36 am

With no UNDEFVALS but Zeroweightoptimization=F its ignores the 0 Values in the AVG. :(
because I'm curious - why do you have so many elements with a weighting of 0?

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » 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 :)

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Thu Sep 05, 2019 11:39 am

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

HighKeys
Posts: 30
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.8
Excel Version: Plus 2013

Re: ConsolidatedAvg & UNDEFVALS

Post by HighKeys » Thu Sep 12, 2019 8:06 am

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

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Thu Sep 12, 2019 9:39 am

HighKeys wrote:
Thu Sep 12, 2019 8:06 am

Now i got your point, the weightings are mostly "1" but it dont help me with an average or?

depends on what you want..... like always in live

why are the weightings mostly 1 and not allways?

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
noooooooo. don't do that. that will not help.....

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?

User avatar
orlando
Posts: 108
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: ConsolidatedAvg & UNDEFVALS

Post by orlando » Thu Sep 12, 2019 9:39 am

HighKeys wrote:
Thu Sep 12, 2019 8:06 am

Now i got your point, the weightings are mostly "1" but it dont help me with an average or?

depends on what you want..... like always in live

why are the weightings mostly 1 and not allways?

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
noooooooo. don't do that. that will not help.....

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?

Post Reply