TM1 Forum

Hello guys,

Im experiencing a problem on summing up the child values generated by the rule i made. See my attached screenshot.

This is my current rule:
SKIPCHECK;
['Number Buying Accounts']=IF(ELLEV('tsUBA Accounts',!tsUBA Accounts)=0, IF(['Product Lines Bought']=0,STET,1),1);

What do i need to do in order be able to sum up the child values?
Your help is greatly appreciated.

I'm not a rule expert (so maybe i'm talking ****) but
- you can define that the rule only points on N: Elements
- or you exclude the Consolidation with stet from the rule

depends on what you need

regards,
martin

You have two problems

1 Write your rule so it applies to the N level only
['Number Buying Accounts']= N:
IF(ELLEV('tsUBA Accounts',!tsUBA Accounts)=0, IF(['Product Lines Bought']=0,STET,1),1);

2 You will need to feed the rule (or remove the skipcheck not recommended).

Feeders;

['Product Lines Bought']=>['Number Buying Accounts'];


If I'm understanding what your trying to do (count the number of prodict sales). I think the last ",1);" needs to be 0); or you will be conting all 0 level accounts irrespective of Product Lines Boght. If your rule is correct as written with the last 1 in place, then I think your rule is "impossible" to feed since the last 1 is not dependant on any other value in the system.

HTH don't have tme for more detail...
Cheers,

I think you need something like the following:

SKIPCHECK;
['Number Buying Accounts']=N:
IF( ELLEV('tsUBA Accounts',!tsUBA Accounts)=0 & ['Product Lines Bought']>0
,
1
,
0
) ;

Feeders ;

['Product Lines Bought'] => ['Number Buying Accounts'] ;

Consolidation in the tsUBA Accounts dimension which I presume has one entry per buying account at the base level, will do the rest. You just need to make the rule generate a 1 for each buying account, at the base level.

Hi Guys,

What if in a cube I am having price which at a child item level is an input and an average at consolidation.

For Eg:-

Jan Feb Mar Q1
10 20 30 20

Is there any function in TM1 which can handle this?

Regards,
Girish

This is very common Girish

Set up a rule restricted to the C: level only saying something like

[ 'Price'] = C: [ 'Sales Value'] \ ([ 'Sales Units'];

If you want to be very careful you might also have

[ 'Price'] = N: Stet;

which will ensure that the Price is enterable at base level

I've put in a post here to suggest best practices for Averaging as it is indeed a common issue with TM1.

Cheers,
Martin