consolidation multiplication

[nicola531]

Hello everyone,

I've got an issue with consolidation.

Normally the consolidation of an elmenent is a sum of the children.
Is that possibile to change this in consolidation in multiplication in some way?

thanks in advance for your answer.

Nicola

[Wim Gielis]

Hello

No. To do multiplications, you need to write rules and feeders in the Rules Editor associated with a cube.

[Eric]

Rules are an option; however, the memory consumption would be more that just a consolidation. You can edit the element properties within a consolidation and apply a weight. This will work like multiplication.

HTH

[Alan Kirk]

Rules are an option; however, the memory consumption would be more that just a consolidation. You can edit the element properties within a consolidation and apply a weight. This will work like multiplication.

But only against that element... weighting can't multiply one child element by another child element, which I think is what the OP was after here.

[nicola531]

Rules are an option; however, the memory consumption would be more that just a consolidation. You can edit the element properties within a consolidation and apply a weight. This will work like multiplication.

HTH

What weight should I put to run the calcule as a multiplication?

-1 is sub
+1 is addiction
form multiplication?

thanks in advance for ur help

Nicola

[Alan Kirk]

Rules are an option; however, the memory consumption would be more that just a consolidation. You can edit the element properties within a consolidation and apply a weight. This will work like multiplication.

HTH

What weight should I put to run the calcule as a multiplication?

-1 is sub
+1 is addiction
form multiplication?

thanks in advance for ur help

Nicola

What are you trying to do, exactly?

If your consolidation consists of:

Code: Select all

Consolidation1
    Element1
    Element2

Then if you want the result to be (Element 1 * 2) + Element 2 you would weight Element1 as 2.

If you want it to be Element1 * Element2 then as both Wim and I have said, you can't do that with consolidations.

[nicola531]

Rules are an option; however, the memory consumption would be more that just a consolidation. You can edit the element properties within a consolidation and apply a weight. This will work like multiplication.

What are you trying to do, exactly?

If your consolidation consists of:

Code: Select all

Consolidation1
    Element1
    Element2

Then if you want the result to be (Element 1 * 2) + Element 2 you would weight Element1 as 2.

If you want it to be Element1 * Element2 then as both Wim and I have said, you can't do that with consolidations.

I think you've already given me the answer but just to clarify myself..

I would like to do this.

Element1
SubEl1.1
SubEl1.2
SubEl1.3

I would like to put the Consolidated element "Elment1" as SubEl1.1 * SubEl1.2 * SubEl1.3.
And I can't specify a rule that says [Element1] = [SubEl1] * [SubEl2] * [SubEl3]
because if another element (SubEl4) occurs in the hierarchy, through a TI process, this rule wouldn't be useful anymore.

Is there any solution to do this?

Thanks in advance

Nicola

[Martin Ryan]

Sounds like a simple if statement will do the trick

['Element1 '] = N: if(['SubEl1.4']<>0, ['SubEl1.4'], ['SubEl1.1'] * ['SubEl1.2'] * ['SubEl1.3']);

HTH,
Martin

[lotsaram]

Sounds like a simple if statement will do the trick

['Element1 '] = N: if(['SubEl1.4']<>0, ['SubEl1.4'], ['SubEl1.1'] * ['SubEl1.2'] * ['SubEl1.3']);

HTH,
Martin

My read was that the "parent" needs to be calculated as the multiplication of all the "children". Therefore if a 'SubEl1.4' is created the the rule would need to change to:
['Element1 '] = N: ['SubEl1.4'], ['SubEl1.1'] * ['SubEl1.2'] * ['SubEl1.3'] * ['SubEl1.4'];

Since you can't reference an element in a rule that doesn't yet exist this would have to be done after the fact. So would require too much maintenance in a changing dimension to be a viable solution.

[Martin Ryan]

Ah, I see, that does pose a problem.

I see a work around but it's not pretty. Can you describe the end result you're trying to achieve with as much actual information as is possible without revealing sensitive information?

My work around would be a lot of nested ifs and probably two dimensions with the same information shown different ways - with and without the hierarchy. Alternatively a TI solution instead of nested ifs but to give you a readable answer I'd like to see what you're trying to achieve.

Martin

[LoadzaGrunt]

So would require too much maintenance in a changing dimension to be a viable solution.

A possibility would be to ELCOMP through the consolidation and do a bit of RuleLoadFromFile-ing after some AsciiOutput-ing ... ?

I'll get my coat then.

[Alan Kirk]

So would require too much maintenance in a changing dimension to be a viable solution.

A possibility would be to ELCOMP through the consolidation and do a bit of RuleLoadFromFile-ing after some AsciiOutput-ing ... ?

I'll get my coat then.

That thought crossed my mind too, but I decided not to mention it because I envisaged dozens of Forum members having fried and popping circuitry between their ears. I think that Iboglix intentionally avoided providing a RuleExportToFile function specifically to avoid such a situation.

[lotsaram]

I agree, in theory possible (but don't forget to skip the "FORMAT==100" 1st line of the rule file in later versions). Almost as ingenious as using TI to ASCIIOutput html code to create "dynamic" web pages, ... but not a solution for your "average consumer." Hence my call that if this kind of multiplication is what Nicola meant then doing this via rule is not worth the effort to automate. Much easier to use ELCOMPN to while loop in a TI and do the multiplication there and send in as a value.

[Steve Rowe]

I think this can be done with rules without too much extra work.
You will need an extra measure in which to do the work though.
Let's say the values we have loaded too are in Units, we will do the work in Product, the parent of the children we are working on is called 'Parent' and the dimension of those elements is dimname'. The rule would be something like the following.

EDIT HAD REASON TO LOOK BACK AT THIS AND THE BELOW APPROACH DOES NOT WORK SINCE ElCompN DOES NOT DO WHAT I THOUGHT IT DID......

Code: Select all

#Set up the initial conditions for the product calculation.
#Make the product of the last child of parent = unit
['Product']=N: If (!dimname @=
ElComp ('dimname', 'Parent', ElCompN('dimname','Parent')),
#Then we are on the last child
['Unit'],
#Else allow other rules to apply
continue);

#Do the general case rule where we multiple this elements Unit value by the previous element in the parent structures 'Product'
['Product']=N:
#Check to see if we are on a direct child of Parent (it's possible this may be redundant can't tell without testing)
If ( ElComp ( 'dimName' , !dimName,'Parent')=1,
#Then do the calculation
#This periods units
   ['Units'] * 
#Previous childs product
   DB ( cubName,..refs to the other dimensions, 
#construct the reference to the previous child of parent
   ElComp ('dimname', 'Parent', ElCompN('dimname',!dimName)-1),
#rest of the DB reference, make sure you reference Product
   ,..refs to the other dimensions, 'Product'),
#Else we are not in the correct location so set to 0 (may want to use continue instead? see my comment about generalising this further)
0);

#The answer we want is in the product of the first child of the parent so rule this to where we want to see it
['Units']=C:
DB ( cubName,..refs to the other dimensions, 
#construct the reference to the previous child of parent
   ElComp ('dimname', 'Parent', 1),
#rest of the DB reference, make sure you reference Product
   ,..refs to the other dimensions, 'Product');

Feeder;
['Units']=>['Product'];

Anyway I've not tested this but I'm pretty sure it would work, it's pretty much the same logic you use for stepping through a time series but used within the dimension structure. You could generalise it further by giving each element a parent attribute and then replacing every 'Parent' reference with Attrs('dimName', !dimName, 'Parent'), this you would mean that you could run the product along a whole structure. You would have to take care that you didn't include pieces of different hierarchies as I think the logic could break.

Anyway I hope the above makes sense, I'd be interested to know what the business reason for what you are trying to do is? It's pretty unusual
Cheers,

[paulfelce]

I am trying to achieve something similar, except instead of multiplying the elements, I want to calculate an average. The problem seems so simple that I'm sure I must be missing something. I've been staring at it for a few hours now and my head hurts.

My position is more along the lines of.....

I've got a dimension of products, and a measure of price.
The products are loaded dynamically by a TI process so I never know in advance what they are going to be.

I'd like to define a 'product' called Average Product, the price value of which is the average price of all my products.

I actually want to achieve something more complex(like excluding some certain products based on other dimensions) , but if I could create the rule to figure out the average, I'm sure there should be enough for me to figure out the rest!

--EDIT-- I guess this means I need a way to construct a 'total' that can add up all other members of the dimension.

[Martin Ryan]

There are a few ideas for averages here and here. You can use the search feature to find some more posts as this is not an uncommon problem.

HTH,
Martin

[paulfelce]

There are a few ideas for averages here and here. You can use the search feature to find some more posts as this is not an uncommon problem.

HTH,
Martin

Thanks Martin,
I've got it working. Scott W's post was really helpful, especially the side comment about why we need the ghost to prevent a circular reference.

It still feels counter intuitive to have to calculate the average within the Measures dimension rather than calculating the consolidation along the dimension I'm interested in. And annoyingly I'll need to have a rule for each measure in my measures dimension, but I can do with a bit of typing practice!

--EDIT--
Ps i s there a null concept in TM1, some of the values I need to include in the average are zeros. I can do a workaround by providing a 'special number' for either zeros, or perhaps mark a cell in another dimension, but I'd hope there was some way of handling nulls?

[Martin Ryan]

And annoyingly I'll need to have a rule for each measure in my measures dimension, but I can do with a bit of typing practice!

You could add another dimension called 'Average' that does the averaging for every member of your measures dimension.

Ps i s there a null concept in TM1, some of the values I need to include in the average are zeros. I can do a workaround by providing a 'special number' for either zeros, or perhaps mark a cell in another dimension, but I'd hope there was some way of handling nulls?

There is undefvals, but it's not well thought of. Your 'special number' is a common work around.

Martin

[Duncan P]

Sorry if this is reviving an old post. I came across it in a search for something else and have an alternative answer to the original post.

If you create a new measure (called e.g. "logarithm") and calculate it using the "LN" function

['logarithm'] = N:LN(['data']);

then at the consolidated level use

['data'] = C:EXP(['logarithm']);

you will get the product of the original data.

You need to account for LN(0) being #N/A so it actually needs to be more like this

['iszero'] = N:IF(0=['data'],1,0);
['logarithm'] = N:LN(['data']);
['data'] = C:IF(0=['iszero'], EXP(['logarithm']), 0 );

Obviously the appropriate feeders would need to be set. Whether this is good enough will depend on the accuracy required and whether too much is lost in the conversion to and from logarithms.

[Steve Rowe]

Wow proper maths!

I always wondered why those functions were in the rule set....
+1 from me and would probably evaluate much faster then my approach which has a lot of hierarchy testing.
Thanks for sharing