How do i sum up the values generated from rule?

Post Reply
crash0verride
Posts: 5
Joined: Mon Oct 20, 2008 10:03 am
Version: TM1 9.0.1.181 SP3
Excel Version: MS Excel 2003 SP3
Location: Manila, Philippines

How do i sum up the values generated from rule?

Post by crash0verride »

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.
Attachments
Clipboard01.png
Clipboard01.png (21.5 KiB) Viewed 5211 times
Martin Erlmoser
Community Contributor
Posts: 125
Joined: Wed May 28, 2008 1:22 pm
OLAP Product: TM1, Cognos Express,..
Version: 9.1.4 FP1
Excel Version: 2010
Location: Vienna
Contact:

Re: How do i sum up the values generated from rule?

Post by Martin Erlmoser »

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
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: How do i sum up the values generated from rule?

Post by Steve Rowe »

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,
Technical Director
www.infocat.co.uk
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: How do i sum up the values generated from rule?

Post by paulsimon »

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.
gnampoothiry
Posts: 14
Joined: Fri Oct 24, 2008 9:18 am

Re: How do i sum up the values generated from rule?

Post by gnampoothiry »

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
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: How do i sum up the values generated from rule?

Post by John Hobson »

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
John Hobson
The Planning Factory
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: How do i sum up the values generated from rule?

Post by Martin Ryan »

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

Cheers,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Post Reply